About the books for CIS103 this term, Spring 2013. The book should be avaiable at the school store.

The code necessary for using the MYITLAB software to hook into your instructor is CRSABR8-851389

Attention: my Email seems to be back to normal

There are two parts to your Word grade. Below, we keep trtack of the scenario portion. By Jnumber, I am listing out the max scenario grades per student that has been recorded on myitlab as of noon on Friday. You will have until Noon on May 4th to do the scenarios on MYITLAB and have them count for 24% of your grade as indicated in the syllabus.

Every year there seems to be some new component for students to get involved in. Some terms it is the question of purchasing the book and getting on the publisher's web site. Other times it deals with the need for attending classes. This year the focus seems to be on whether one can get out of taking this class.

Keep in mind that your instructor is part time. He is not involved in course curriculum or administrative procedures. He does not keep track on any other students than who are in his class. He certainly is not writing letters to advocate student assumptions. So, below, with permission, I have included an Email correspondance with Chuck Herbert who handles these questions for this department. Please read this carefully before bringing these and associated issues up to your instructor or in class.

I want to make sure that all students are aware of what I will say in class which is repeated here. You are required to be on the myitlab web site and integrated with my course on that site. Your two tests on Word will be done through the site. These have been set up already as you will be shown in class. They will be available through the entire term although the instructor needs to fill out school forms which require him to use the grades as found at that time.

For this term, Spring 2013, the MYITLAB site ised for this term is still being copied. Hopefully we will see the codes in class today although priliminarily it is indicating CRSABR8-851389. The code is what you should use as you register. At the moment, nothing has been set, I hope to have this set for you as of week end.

As to files: There is a CD that comes with your book. The CD contains the files you need to work the problems in the book. if you are at the school, you can see these files on what is q-shared drive. This is the school network.

Click Computer technologies and one of the folders will contain these 103 student files. However, I have saved these files on this web site. Click the button to see a list.

To the left is what you should be seeing when you register for the MYITLAB web site. At the bottom you will see the three tests. You are required to do the following: The multiple choice test for Chapter 1 with a tag line of 'This is the multiple choice test for CIS103, Spring '13'. Your grade will be the highest attempt of a max three attempts. Each attempt is 7 minutes. In addition, there are two scenarios with tag lines pertaining to cis103 fall'12. Each scenario can be done three times for a max total of six attempts. Your instructor takes the max grade of these six possible attempts.
To the right is the parameters I have set for the scenarios. You have 3 hours for each attempt and you can see that each scenario has a maximum of three attempts. The system should provide me with your max score.

For MWF, click here to open the cbl problem where we ended it on Friday.

And, if we have time, we will do the Old Forge School for Cats problem what can be found by clicking here.

For the Thursday class, we are going to deal with the following problems:

The books of Ian Fleming problem.

The next problem can be found within an access database, cbl.accdb. What we are to do is explained by clicking here.

For Wed, April 17th, we can start where we ended on Monday by clicking here.

We want to create a calculated field at the total level trying to determine the average profit for each individual copy of book sold. Below, we show how to create a calculated field.

The next problem can be found within an access database, cbl.accdb. What we are to do is explained by clicking here.

Let's create another pivot table, this time using the continents. Go back to sheet1 (or the sheet with the underlying data) and start a pivot table process all over. Notice that we start on a new sheet. When we are do, let's make a change on sheet1. Notice that there is no change on the pivot tables. You will need to refresh the pivot table for the underlying data to become evident on sheet 4 and 5

The next problem can be found within an access database, cbl.accdb. What we are to do is explained by clicking here.

These are notes from a previous class which we will change as we go along.

let's use the CBL problem with some modification. Again, similar to the last problem, let's set this information into the first three spreadsheets. The first spreadsheet will be by subtotals, the second by new table/pivot tables and the third with an added complication. You can find this at cbl.accdb. Use export as indicated below to create an excel spreadsheet of this table.

Let's change the tabs as indicated above. So, we start with subtotals. The CBL details can be found by clicking here.

We are doing sheet1 by subtotal. Remember, in subtotals, you have to do the prep work. We want to find the average MVP points per team. Just like the previous problem where we needed to get the book titles together, we need to get the players of each team together. To do this, we need to sort on team name and it doesn't matter whether you do this ascending or descending. Once we have our sheet sorted, we move into subtotoals by clicking subtotal in the data ribbon. We are using breaks in the team name to do this. We need to select average and the column we need the info on is the last, MVP points, and that is already clicked. Below, you can see this.

Clicking on control 2 will just show the teams and their average points. Sort on points and you will find that the Boston Braves (who are now the Atlanta Braves by way of Milwaukee) are the winners. Format to two decimal places and compress columns B through M. Now, we only see columns A and N, Highlight the h3eader and team info (not the grand average info) and run a bar chart and you should have what is indicated below.

Onto the next sheet. Looks the same as when we started the first. This time, let's use excel to help with our formula. Home runs are in F2. Move your cursor to N1 and enter MVP points. Now into N2 and put =4*. Now click on F2 and you will notice an F2 is placed to the right of the * in N2. Below, we catch this. Notice that f2 is outlined (Microsoft terminology for perforations around a cell). This will be bordered in some color as we add the next operator, in this case a + for addition.

Now, let's finish this off. Having put in the + (notice the border around F2 has become a normal border), we click on g2 and g2 is now next to the + as n dicated below.

Let's finish this off to get the numbers we had in the first sheet. Remember that the first individual ends up with 57.5 and the last player is 290.5. This sheet is going to be done by pivot table. There is no prep. It's right into pivot tables and all we need to do is click inside the table and click the insert tab and then pivot table. Once into the pivot table, select team name and mvp points. Our results should look like subtotals. Through the pivot table, sort hightest to lowest, format to 2 decimals, graph and format the pivot table. Below, shows where we are at this point.

At thiws point, our pivot table looks like our subtotal info. I call this a 1-d pivot table. Let's see if we can show you something a little more complicated. Each player bats either right or left handed (we assume no switch hitters). Click bats and drag bats fvrom rows to columns as i will show you in class. Now, the pivot table is 2-D with rows and columns. You should be seeing something like the following.

Notice the chart also. This is what is known as a stacked bar chart where two (or more) sources of info make up the bars. Pivot tables have tremendous capability which includes the ability to calculate information of the subtotal (group basis). This is similar to the having command in SQL. let's assume, given this information, that we would like to know the percentage of left handers on all the teams (it is possible that your instructor has picked left handed based on his own biases and you must always be aware that you may be adding such a bias in any statistic you try to determine. In this case, absolutely, your instructor has picked left handedness given that he is a natural left hander).

Now, to do this, we need to add a count here.Drag bats to the value box and since this is a text field, the system will automatically set this as a count. Cut the chart as it will now start providing meaningless information. You should see something like the foloowing.

We want to calculate a field based on this info. We would like to know on a group basis by team, how much effect did the home runs have in mvp points as a percentage for left handers vs right handers. The higher the number, the more home runs influenced this. Add home runs to our values. This should come in as a sum. Now, there is a control designated as formulas. One of the options of formulas is calculated field. This works out math on a group basis. We are showing home runs and MVP points by left and right. Whatever we ask, it should show this by left and right. We are going to ask for 4 * home runs/ mvp points. Home runs and mvp points are already fields. we start by entering (4* after the equal sign. Click the field homeruns. Enter a slash (/). click mvp points. Above this call this field per hr and click add. Notice that this has been added to our list of fields. It should already be clicked, but if not, click it on. Do you see the percentages? Set this to 2 decimal places. Below, we follow this argument with a picture.

We turn our attention to the new table construct. Let's do this by formatting. On the home tab, select format as table. Pick whatever color you want. Before we do our calculations, let's determine several things here. Who were the top 10 home run hitters in the league. Click the home run column header control and click number filter. Now, ask for top ten. Below, we show the process.

You can sort the home run column to see these in descending order. Now, let's answer another question not done before. Who are the top 10 players batting averages. Batting average is the number of hits (homers + singles + doubles + triples)/(at bats - sacrifices). Multiply this by 1000 and you get a set of numbers. In this column, sort descending. We will only look at players with 300 or more bats. Set this as a numeric filter in at bats. Now, back to batting average. Again set a numeric filter and select top ten. You will notice that only 2 show up. This is still considering the players with less than 300 at bats. In both access and excel, logical and really mean what they say. The way to handle this is allow the filter for 300 or more bats, sort batting average descending and then group player 11 through the end together and apply group by compress. Below, you see this.

Move into N1 and indicate MVP points. N has been added to the table.

Now, for our last situation, what if there was an added stipulation that 30 points were awarded to the players that hit the most home runs. Also, the same was true of the most hits. How would you handle this?

You can start with the if statement. It's format is =if(condition, do if true, do if false). Conditions are equal (=), less than (<>, gretater than (>), Less than or equal (<=), greater than or equal (>=) and not equal (<>).

For Friday, April 12, let's start with this file

For the Thursday and Saturday classes, we are starting Excel. You can see some of the documentation at the end of Apr 3rd week lecture. We will discuss some generic aspects of Excel first and then move into our first problem, the BigW auto dealership. Click here for the Big W auto dealership.

After this first problem, a refugee of the early 1980's, let's look at a more modern problem although it is supposed to have occurred in 1965. This is the books of Ian Fleming problem.

Now, here's what we need to do on this problem. We need to congregate all the casino royals together, all the goldfingers. We should sort on the book title. Now, one note. In a previous sort we used the full scale sort icon given that another line item was added for totals. We will not have to do that here. So, we can use the simple sort A to Z or Z to A that we have seen on the Data tab of the ribbon.

We need to calculate the profit for each line item and then propogate this through the data. What calculation are we talking about. You have a number of books by a specific title being distributed, Some are returned and therefore not sold. Each return is at a cost. The books sold are the books distributed minus the books returned. Each of these is at the profit level shown at the end of the line item. The returns are at a cost of 50 cents apiece. Calculate the net profit for each line item applicable to book distribution and book return.

Now, let's copy this relationship down through all the line items. When you are done, your spreadsheet should look something like below.

I have given the new column a title of profit per book and set this new column to 2 decimal places

Now, let's get the books names together. We can do this by a sort. Sorts have changed in Excel for this version. For our purposes, we need do as little as indicate a cell within a column and then click sort. Notice, all the book names havec been moved together. We could have sorted in reverse order by clicking Z to a. We need this congregation because we are about to invoke subtotals. The concept of subtotals goes back before WWII, which is a long tome in data processing. Tab operators used sorters to accomplich the same thing we will do. What was the concept. Organize data and give subtotals on breaks of data. What is a break? A change in value. The first column is set for this.

We need to know the books distributed, the books returned and the profit for each book. At a break in the book, we need these subtotals. Wouldn't be so bad either if we could get a full total of all the books sold world wide.

A warning is necessary on this. Most students when doing this in a test, forget to do their sort. Or if sorting, use thec wrong column. You must think about the ultimate data you want. The Fleming heirs are asking the accountants for a breakdown by book title. It would be counterintuitive to assume that anything else in this spreadsheet should be sorted, for example, how would we resolve the Fleming's request if we sorted on continent. Continent would be viable if the Fleming's had requested information by continent, but this not their request.

Anyway, we are almost done with this problem. On the data ribbon you should see subtotals. Let's click this and enter the info as indicated below. We want to create subtotals based on book names and need subtotals where te names change. We want t sum our results and you can;t mix and match i.e. it's either sum or average or multiply for every column chosen, and finally, what columns to do these calculations on. So, below left, we have prepped subtotals in its dialog box. Below right you can see the results which we will talk abou below these pictures.

Notice that the columns chosen in the dialog box are summed for each book. The best way to look at subtotals is by the controls to the left. Click 1 and you will see only the totals. Click 2 to see the subtotals for each book and 3 is back to both line items and totals. Click 2 again, We can sort these but we need to use some smarts in doing this because we do not want to move grand totals out of its position. This is easiest done by getting this row outside of the table. Insert a row at grant total so that grand total is separated by a null line. Let's sort by profit similar to what we did before. Further, let's make a graph for the flemings. Remember what we did for the Big V auto dealership. You can create a graph the same way. And we can group the same way,

We should have time to do this problem by pivot table, which is why we have primed sheet 2 & 3 with the same data, and you can see a write up on Pivot tables (using Word 2003 in this example) by clicking here

Let's look at the tabs for each sheet at the bottom. Sheets can be renamed. Right click on a tab and one of the options is rename. The tab text is highlighted and therefore ready for overtyping. Let's change our tab names to subtotal for sheet1, pivot table for sheet2 and new table for sheet3. Below we show this.

We are not going to use this feature today but let's assume you need to access another sheet while on your sheet. The official way is ='sheet name'!a1 for instance. This would result in 'pivot table'!a1 if referencing sheet2. If no blanks are embedded, one can ignore the quote.

As we were supposed to do last time, we are going to look at pivot tables as indicated below. First, we have to calculate profit. Let's assume you have aexibility with math. You could resolve profitability per line item with the following equation =(d2-e2)*f2-e2*.5. Use this to populate a profotability column.

In the pivot table spreadsheet what are the possibilities to the problem. The family wants to know the profitability per book title world wide. This is a 1D problem, but we will be using a pivot table to resolve this. What do we expect as the titles of the row in the table that will be built. It should be the book titles. What should we expect in the cells of the table. Each book title encountered adds to the total for that book title that had already been accumulated.

We start the process by clicking thr Pivot Table control in the insert tab of the ribbon. Thhis should give you the limits of the table. Note. Pivot tables need column headers to work. If a column does not have a header it will either not be included or the pivot table will fail. We have designated our new column (for line item profit) as profit. Although there is no requirement, put the pivot table on a new spreadsheet (this is the default). When the pivot table appears, click title as indicated below. Notice at this point, we have listed the book titles in sequential order and the designation title is in row labels. That's exactly where we are at.

Now, we are looking for aggregate profit per book. Click profit and you will see the pivot table below. By the way, this is not magic. Book title is obviously a text column so when clicking such a column, the system added the designation to the row label bucket. Profit is a mathematical column. Math columns get added to the values (in previous versions designated as data) bucket as you see.

These books are already sorted by name. Probably, the family would like to see the titles sorted in descending order for profit. Your pivot table provides tabs that support its operations. One of which is sort. Look at that section, move your cursor anywhere on the column representing profity and click z to a. Below we see the result.

You know that we can chart in Excel. Charts also exist as far as pivot tables are concerned. Again a bar chart is the best for this problem. Again, look at the pivot table tab. Chart is an option. Click that and select column chart type. A pivot chart is produced with more capability than previously seen. This added capability is geared to turning on or off the charting of different titles.

You have formatting options to this pivot table also. Click back onto the pivot table and click the design tab. I have added to this by clicking banding row and selected a gray (some say to go with my personality) format. Below we see the result.

As promised, we are about to deal with detauil info, sum that up into subtotal info, then deal with this subtotaled info for an answer. You can look at this in one D, or in Two D, For Two D (and above) we have 2 possibilities: Pivot tables and the new table constrcut breaking down into pivot tables.

Before returning to subtotals, let's show you a taste of 2D. Move your cursor to continent and drag that into column labels. Once done with that, click in the chart and request a stacked bar chart and you can get the following.

Pivot tables allow great flexibility. We will see the following possibility when looking at the new table construct, but you can select which values of the column (or row) to view. Below, we have dealt with North and South America in the columns.

Another thing you can do is calculated fields. This is tough to control but lets assume you wanted to know the average profit of a particilar book world wide. This is total profit divided by number of books sold. We can calculate this but you first must understand why this is a difficult calculation on our spreadsheet using subtotals. If I can explain the difficulty of this by subtotal, we will look at the creation of a calculated field in excel.

Sheet3 deals with the new table construct.

For the MWF class, here's where we left our problem on Monday.

For the Thursday class, I will be late by a half hour this morning (April 4,2013). I thgink we need a little more exposure to Access before taking the test. I have copied the census info from last week and will finish the problem.

For today we want to look at the basis of a relational database. What we are really looking at is the inter relationship between different tables. To look at this we will use a database consisting of 3 tables. One table consists of states with population indications. Another shows the state with the capital of the state. The third indicates the military bases operating in each state. You can access this database by clicking here.

I have put a query into the database called pop per sqmi. One class got to this, the other did not. so let's start with a join. Add to the tables being shown with capitals. if not already, set the link between state of states and state of capitals as shown below.

Here's the power of relational databases. Links designated as joins allow the interchange of information between tables. inner joins, the one we have here, go 1 to 1. If somethng is missing in either table, it is not shown in the result. Left joins in Access (and Access does not do all the possible combinations that higher level SQl databases allow) look at states, and state within states. All the entries of states.state are shown even if a link does not exist. The same on the other side, known as a Right join. All of Capitals.state would be shown whether or not a corresponding link is available in States.state. Below, we see the beginning of this.

The easiest way to add a column in the grid is to highlight where it should show and click insert columns above in the design tab. Below, we've done that.

Let's finish this out by adding the bases table similar to what we did for capitals. Notice the bases table has abreviations for state designations. Abbreviations also exist in capitals. If it doesn't do it already, set a link from abbrev in capitals to abbr in bases. This should prove to you that there is no need for field (or column) names to be consistent between tables. Now, let's add as fields base and employees from bases and run that. The result should be similar to what's below.

Notice that we have shattered our 1 to 1 relationships. There are many bases per state in most cases and thios is what is shown. Because Access is a lower level SQl database, it is not as flexible as other SQL databases and you will have limitations as to inner, right and left joins. Generally I have found that Access will access a SQL statement consisting of all inner joins, all right joins or all left joins. We mention this because there are bases in all the states and we are only displaying those bases in states with pop2010 > 6 mil and pop density > 100.

We would like to determine the population of the 50 states. To do this, we invoke aggregate functions. Here's where there are real limitations to how SQL is syntaxed. Let's start slowily. In SQL view, just put in select sum(pop2010) from states. The result is 309183463. The sum is an aggregate function. Let's find out the state average. Substitute avg for sum. The average state has 6183669.25 population. All right. Go into design view. You can see a total row has been added which allows you to enter these aggregate functions. Now, add the bases table. We would in addition like to find out all the employees of the bases per state. Notice, we have no link. States are spelled out in states, abbreviated in bases. Let's add the capitals table to rectify this. If the links don't show up, set them.

Now, let's add state name as the first column. Somethng been added. Do you see the group by. Without group by, SQl stops in its tracks. Aggregate cannot be displayed with specific line item info in Sql unless that line item data is acknoledges to be a group by. QBE handles this for you; Sql view does not. To finish this problem, click for employees. The system with set group by as a default. Click for sum. ANd then let's run. If you did this right, you have the population for the atate and the sum of the employees for the bases that reside in the states. Below, we show this,

We still have to resolve the underlying problem of last class. What is the percentage of a state's population that works in military bases? You can access this database by clicking here.

Below is our calculation to determine the sum of the employees in military bases per state (this is subtotaling using group by), taking the population of each state (detail so part of group by) and then doing a calculation involving a detail and aggregate.

I don't know if this was in Access 2007, but I do notice that Access 2010 contains a crosstab option. I have been experimenting with this and I think we can do it in class. The following images below show the creation of a croostab. Here's the first showing how QBE deals with this.

Below is the result. Notice that there are no totals. Totals can be set in similar manner that you will see in Excel table structure. It is inconvenient to have to set each column independently of course.

Our test in Access is ready. I hope to present you an example today. Your specific test will be ready this afternoon. click here to access a specific script.

Unit 3 of your book does Excel under the title 'Electronic Spreadsheets.' For your own info, thec term derives from regular spreadsheets which are extended length ruled (as column and row) sheets of paper that bookkeepers and accountants have been using since the late 1800's.

We will be doing a different problem - one that emphasizes relative addressing. But, in following me, you should be reading and following the book although you will be using my test.

Excel is very mathematically oriented. It uses the mathematics you were taught between the 7th and 11th grade. If there was a question in your mind whether you would ever need that stuff, well, here it is.

Anyway, your instructor should have (or will) discuss the following points with you about excel as we start

Similarities (and differences) of the ribbon structure to Word
Navigation around a spreadsheet
Use of multiple spreadsheets in a file(What Microsoft calls a workbook)
Bias in entering data - what's horizontal and what's vertical. If we don't discuss it, modern spreadsheets line items grow downward, fields of data move rightward
Types of data that can be entered per cell - numeric, alpha and formula
Selection of multiple cells and use of ranges. Note:starting several versions back this was extended but for this class we keep it to the rectangular view.

Throughout our discussion of Excel we should be doing multiple problems demonstrating it's usage and, as mentioned above, the book has its own problems for you to look at. You, as a student, should be doing all these problems at home and/or outside of class alone to build up your expertise. Your excel test will be in class on Monday, Dec 12th. It is very important to continually try these problems as this will train your mind into this type of thinking which is not bad thing to know in life but especially not bad for the test.

For this week's class, let's do the handout - the Big w auto dealership which you can access below. This is a very rudimentary problem which is based on 1985 usage of excel. In dealing with this problem, we are starting our discussion of the nuts and bolts of excel. Starting from addressing we can deal with:

Relationship logic which leads to absolute and relational addressing. Besides giving examples of this in class, you can click here to bring down a word file with a discussion on this.
fills (which act as copies in Microsoft word)
Using ranges, determine sums, averages, counts, maxs and mins
Discuss the use of Ifs. Note: Excel has become in reality a programming language whose face is what you see. In programing languages, if statements provide the beginnings of code control. In Excel, if statements are a basis of controlling the logic of data.

The hardest part of this is understanding relational addressing. Keep in mind that when you put =a2+1 into a cell, excel does not set this as the specific cell a2. if uses an offset (or difference) to tell itself how many rows and columns from the entered cell a2 is. If you are in a4 while putting in =a2+1, excel sets this as a offset of =2 rows, 0 columns. If you are in a1 and set =a2+1, the offset is 1 row, 0 column. If in c2, the offset to =a2+1 is 0 row, -2 columns and in the case of g5, the offset is -3 rows, -6 columns. It is this offset that is used for determining relationships. However, in the case of absolute addressing, =$a$2+1 for instance, a2 is always used.

If you understand relational vs absolute addressing, excel becomes very easy. A 2000 line item table (rows) is as simple as dealing with a 5 line item table with the only difference being the physical need to move through the line items. To this effect, Microsoft has developed the new construct, table, to deal with this. Now, in class, before dealing with the problem for the day, your instructor will show you some examples.

We are going to look at a problem that your instructor has previously done in classes. By time we're done, we will have done many of the techniques discussed in every Excel instructional book. But before we start, you must understand that the use of Excel has evolved over the years. The problems we are doing, and the techniques your book describes at the beginning are circa 1985 in use. Today, Excel is used as a supplement to database info (and similar type situations). There is an expectation that raw data is already available in some form (generally from a database although your instructor deals professionally with a set of problems where data is generally kept on excel spreadsheets) and that this information will need at minimum something called subtotaling. Once this is done, Excel's excellent chart (graph) tools finish up the project.

However, for our first problem, we are looking at entering our own data - we have no where to retrieve it from - and this data has already been subtotalled. Nevertheless, this problem should give us an idea about entering data and formulas, looking at the normal bias of excel, use of ranges and fills, use of various functions and the use of graphs.

So, if your instructor has not given you a handout - or you are going over what was done in class - let's bring up this problem and first discuss it in terms of what you know business to be and to need as far as information is concerned and then we'll start to apply some excel on it. Click here for the Big W auto dealership. Please note: I have changed the underlying document but the pictures (screen shots) use the previous instance of the BigV dealership.

Before discussing this problem, keep in mind that 6 salesmen is not a reasonable assumption today, In real life threr would have to be more salespeople and this is not even considering that most auto dealerships are chains. In doing this problem, we will handle it as if there were many more rows. This would be in preparation of further problems this term that will contain much more information.

In addition, all spreadsheets for commercial use have headers. We will ignore column headers as we start this problem and they will be put in later. Use A1 through A6 to enter the salesman's name. Use B1 through B6 to enter the amount sold for last month. Notice that excel is giving you informatiin as you enter the data. Non numeric data is left justified, Numeric data is right justified. Notice that Excel has not made any assumption on decimal points and as you put in an integer, that's what's there. Since this is money, should it be left as an integer? Do you have any other options when imputting? Yes, you could enter commas as separators but we will not be supporting that in this problem.

Even before putting anything else in, let's handle column width. Widths start out as standard. At minimun, you can manipulate column width by placing your cursor between two columns at the top (where the colunn indicators are) and use the cursor you aee at that point (I call it a grabber) to change column width. Below, you can see this in operation as both column widths have been decreased.

Besides the movement of the columns, look what happened to the data. Text is truncated. Numerics become first scientific notation (one of the allowable entries of numeric data on this system) and then pound signs. Excel is geared for math and it wants to make sure no decision is made on wrong assumptions. If it is trancated as done in string data, one might make a wrong decision seeing the wrong numeric entry. You can bring this back by invoking autofit for column width. A poll is taken on the width of each entry in a column and the widest entry determines the width of that column. If you can fit the widest, all the others will also fit, of course. There are ways to do this by the ribbon which your instructor will show you and by mouse. In terms of mouse, take the column in question and at the right gridline of the column description, double click.

In this problem, each salesperson gets $12000 a year split over the year. That is $1000 a month. Now, we could put in a 1000 for each entry next to a salesperson (column C), but we are going to use some math and relational operators to do the job. Math uses operators to determine what to do with numbers. Common operators (for real math and Excel) is + (addition) - (subtraction) * (for multiplication) and / (for division). To tell excel that you are dealing with a math operator, you need to start the entry with an equal sign (=). If you don't, you violate what excel feels is numbers and your entry defaults to text. The mathematics of this is 12000/12. Put this in at c1. Notice this is left justified and no calculation has taken place. Now try =12000/12. The equal sign indicates to exc el that we have a formula. 1000 shows up indicating that a calculation has occurred and it is right justified. Congradulations, you have done your first calculation albeit an easy one. Now, this is important. There are many things that make excel powerful and one is the ability to replacate relationships. The easiest relationship is a calculation. Move back into c1. There are 3 cursors in excel. The big plus sign is used to select cells and this is a way of creating ranges which we will talk about shortly. At the bottom right of any border in excel one would see a crosshair and put your cursor there now and you will see what I'm talking about. Finally, at the bottom left is both a crosshair and normal mouse cursor symbol combined. This is for moving and would be like a cut and paste in Word.

Now, in C1, move your cursor to the right bottom. When you see the crosshair, click your left button and drag the crosshair down the column. Below, you will see an example of this.

Let go of your mouse button and you will have copied this information. Waht was copied. Your relationship. You could see this by moving into each cell in column c and looking at the formula bar. It is easier to look at the screen with just formula. Go into the formula tab of the ribbon and on the right click show formulas. Below, you can see the result.

So you can see that you really did copy the cells as far as formulas are concerned. Now, hit show formula again to return to normal mode. We need to calculate the commission defined in our documentation as one percent of the sales amout for each salesperson. First, what is 1%. One could look at this as 1/100. Also as .01. And these are perfectly reasonable. But excel allows you to use 1% which is the easiest. Now, how do we calculate the first salesperson's commission (B1 for the sales, D1 for the result). We could use =1%*240000 or =240000*1% - this is one of the laws in our math which you should have learned in high school. This will work but it is limited. What would happen if the 240000 sales figure was changed (a late sale, a sale that was missed, or a busted sale where the figure goes down) WE would need to change this twice. One in B1 and the other, here, in D1. It is easier to reference the value of a cell than continually use the number. Therefore, our math relationship in D1 should be =1%*b1 or =b1*1%. Put this into your spreadsheet. Now, I am going to use E1 to put in what was originally proposed: use the math expression =1%*240000. Now, let's change B1 to 200000 (a sale of 40000 did not go through), Look at the result in your spreadsheet. Below, we have the example.

See the difference. The moral of this story: use references to cells. Now, let's do the same thing to column D that we did to C. Move your cursor to D1 and its right bottom border. Use the crosshair to fill down. Notice numbers are being put into the cells. They are 1% of what is 2 columns to the left. Use show formulas again and you will see what I have below

Notice that B1 was changed to B2 through B6 as we went along. You didn't do this. You had originally set B1. If B1 would have showed up, you would have been dealing with an absolute address. Excel determined that B1 was 2 columns to the left of D1 and 0 rows offset. As you filled into D2, the corresponding cell would be 2 columns to the left and 0 rows offset. Of course, we are therefore talking about B2 in this case and this was substituted. Look at the others: you will notice that this analysis is working for all these cells.

Now, look at D1. you should be able to see that it references b1. How does Excel look at this. Not as the cell b1. it looks at this as the cell 2 columns to the left. As we copy down this relationship, as it hits a cell and needs to enter a formula, the reference to a cell two columns to the left is enforced. Now copy this down to fill d2 through d6.

Previously, we have clicked within cells to see the formulas in the formula bar. Is there some way of doing this on a more geenral basis. Yes! In the formulas tab, on the right click show formulas and you will see what we have to the right. This is a good tool to use to see formulas in general as it lays it out on the spreadsheet. Ypu can even operate your spreadsheet this way in terms of entering formulas but you would not see the results. Your instrcutor would recoment that every now and then you click on this, especially if you have a big and complicated spreadsheet, to see if the patterns of the formulas make sense.

Click show formulas again and you are back to the regular aspect of excel. Excel has some other tools that you can use to get a feel whether you are proceeding as planned. Move your cursor to D1. Staying in the formula ribbon, click trace precedents and an arrow will appear emanating from B1. You can eliminate the arrow by using remove arrow/remove precedents. Precedent is a nice way of saying the di uses b1 in its calculation and b1 is 2 columns over to the left.

Reversely, click in cell b5 and click trace dependents. Notice the same type of arrow going from b5 to d5. On complicated spread sheets this can be a help to trace the flow of the data. Below, we have composited these two controls.

While we are on the formula tab, let's indicate a new control for excel in this version and that is evaluate formula. It's use is better seen with more complicated formulas and you should use this as you study for your test in a few weeks, but it will show you the sequence of calculations that excel uses for get an answer for any cell. Move onto D6 and click this control and you will see these calculations in action.

We have one more column to calculate. The sum of fixed and variable. Let's sum up column C and D. E1 will be =c1+d1. Let's copy down. By E6, what do you think will be our formula. If you guessed =c6+d6, you are correct. And keep in mind, this is the same as =d6+c6.

While we are looking at calculation, this would be a good time to talk about ball parking. Excel does what you want it to do. There is no editorial comment from the program. It has no way of knowing or interpreting what is the ultimate purpose of these calculations. It is up to you to make sure that these calculations make sense. I use equivalents of 1% to determine if in the ballpark. !% is easy to deal with since you drop 2 zeros. In other problems 10% is the marker and yuo drop 1 zero. Let's assume that this problem was working with 1.2% commission. I'd still use 1% as my marker doubling the result to look at 2%. The end reult, when applying 1.2% should be between 1% and 2% and it should be biased closer to the 1%. Look at our calculation at this point 1000 for the monthly fixed should have looked somewhat correct based on the statement "12000 over the year". The number in the D column should correspond to 2 zeros being dropped from the values in B. Finally, the calculations should be easy enough to check the accuracy of column E.

Let's continue. Sheet operations are independent unless you yourself change this. You have been working with sheet1 and probably have not realized that sheet2 also has calculation in it. Click on sheet2. Here's are problem again but with 2 columns. Look at the last column indicated in blue. We have been dealing with something called relational addressing. 3 other addressing schemes exist in Excel. For this class, we will deal with one other, absolute addressing. Using trace precdents, click on G6. You should see something like below which is very different than what we have seen before. This is crosiing rows. In absolute addressing, which is indicated by a $, you really are using the addresses indicated. Therefore $c$1 stays as $c$1 as a fill takes place.

Having dealt with absolute addressing, what about column F. The results look the same as E but the calculations are done very differently. We are using a function designated as =sum(). As with many functions, this can be widely used: as an example =sum(first, second, third, four) would be legitamate where first, second, third and fourth are something called ranges or cell or constants (in math). So, this brings up what is a range?

For this class (and this has changed with the additon of the use of the extended selection by way of the ctrl key) a mouse selection that resembles a rectanggle is a range. Take an example. Select c1. Drag your mouse through c6. C1 to c6 make a rectangle and a range. You can designate as c1:c6 or c6:c1. Similarly, we have the same situation with c1 and d1. They make the range c1:d1 (or d1:c1). By placing a range in the sum function, you can sum up all the elements (cells) indicated by the range. So, you see the f1 contains =sum(c1:d1). Ranges are similar to cell addressing in that they can be manipulated. One fill filled up column F.

Let's go back to our sheet1. Column totals wouldn't be a bad idea for B,C,D and E. Can you figure out the fastest way to do this. If you said range, you are correct. We'll show you two ways to do this as we are in class

Previously, we discussed relational vs absolute addressing (and we are using relational for this problem although I may show you an example of absolute at the end of this lecture, today. Manipulation of widths of columns and how Excel deals with numbers when the width is too small vs numerics. We looked at ranges and how these are used with the function Sum() and used Sum() (and autosum) in column totals among other things. You also saw how to turn the spreadsheet into a table of formulas. Now for today, we are going to deal with insertion of rows (possible columns) and setting up a set of column headers. Also, how to gruop columns (and rowsfvor the matter) and the creation of a very limited graph).

We are going to look at insertion. This can be done on many levels including insertion of a cell, a range, a row and a column. Unless you are at the row and column level, a second question is asked of you. Are you moving down the other cells by rows or by columns. You are not asked this question when a row or column is inserted. In additon, there are two ways of requesting insert. The one not recommended by me is the formal approach using the insert control on the home tab as indicated below.

In the case of an insertion of a row (or column) to use the context sensitive popup using the right click of the mouse. To sdo this, click the row that will move down when the new row is inserted. n the case of several rows being inserted at the same time, drag your mouse and select the number of rows where the first row will move down. In our example we need to add 3 rows. One for the title of the table, a blank and then the column header. Below, you see the start of this, Rows 1 to 3 are highlighted. A right click has made the pop up menu appear and we are about to click insert on that menu.

We can handle the title first and it can be as simple as the Big V Auto Dealership. We want this to center over our table. Insert Big V Auto Dealership in A1. It will bbleedc into b1 abd probably c1. Highlight the range a1:e1 and click the merge and center button in the alignment group of the home tab. What we've done here (as mentioned on Wednesday in class) is create a large A1 spanning to F1. And our result should look similar ot what we show below.

Now, we will allow the blank row to stay at row 2 as it is but now let's concentrate on the header row which we will put in row 3. Each cell of row 3 will provide header info for that column. But it's not going to look good as we first put it in. We will have to manipulate the row as you will see. But first, let's enter the info. Column A is Sales Person. Notice how it bleeds into the next cell. That next cell should be Sales Amount. Column C is fixed. Column D is sales Commission. And, finally, column E is total Salary.

One solution would be to widen the columns as demonstrated on Wednesday. While it will work, it will make the spreadsheet look odd with the columns being to big. WHat we would like to do is have the system break the cells so that there may be multi-leveled descriptions. And that's what we are going to do. But first, let's bolden these descriptions and increase the point size. With row 1 selected (and this is done by clicking in the descriptor of row 1 where it says 1), increase the point size to 12 and click the bold button.

Now, you have two choices as to selection. You can use the pop up menu as we have done before and select format cells or the format control on the home tab and select format vcells. Below we show both possibilities.

Click on format cells and you will se a dialog box pertaining to 6 possibilities for the range selected. One, protection, we will not deal with. Font generally can be dealt with using the font group of the home tab of the ribbon. Even number, which is very important, can be dealt with through the number group of the home tab. But alignment still holds importance and we want to click this. Notice there is a check box, merge cells, and in essence this was used to create the title in Row 1 although it is easier to control through the icon we used.

There are two combo boxes which control the type of formatting on a vertical and horizontal level. To start out, use center and center and click the wrap text check box. This is the most important of the control although you would not know it by the placement. Below we show a compoasite of these selections and the result.

Fixed really should be at the bottom and this would have occurred if the vertical controls were set to bottom. Further, you might want to set this text at an angle and the picture below shows this using a 45 degree angle.

If you want, you can set this back. We now want to set up 3 rows below this table indicating for each column, max, min and average. Our table stretches to row 10 so let's use row 12 to start this. To get an averagbe, indicate a range in the =average() function. Likewise for max using =max() and min using =min(). So in B12, set the function =average(range) where range is B4 through B9. Why not use B10?

Similarly, in B13, set up the max and in B14, set up the min. Similar to our totals, these are relationships that can be copied over. But you do not have to do this a row at a time. Excel is smart enough to fill up ranges. Select the range B12 though B14 and then grab the handle and copy over. See how easy this is! Below is a composite of this.

We have our spreadsheet and then some. Suppose we would like to prepare it for a few viewers. But the needs are different for any of them. There is the owner who wants to know what amount was sold and what the salaries were. There is the accountant who need to see all numbers. To resolve this, Excel provides from grouping which on the operating system is called un Or decompress and compress. Here we have group and ungrouping of rows and columns. This is done on a specific entire row or column basis. Group and ungroup is found on the data tab of the ribbon.

Let's start this looking at columns C &D - fixed and variable. The big boss is probably not interested in this so highlight the entire column c and the entire column D by clicking insider the descriptor headings of c and D. Now, click group on the data menu. A new section opens up with new controls. Use of the controls (both to the left and above the columns) allows you to compress C & D from view or make them visibile. Similarly, let's do the same for rows 12, 13 & 14. hen compressed, by the way. a printout will not showe these columns and/or rows so this works even when printing. Below is an example of this.

A graph or chart might look good here. Let's reference the sames person's name and show their salary. What type of chart would work to do this. Probably a bar/column chart. Microsoft calls that we woud normally call a Bar chart, a column chart. For this class bar abd column is interchangeable and you can do either when asked to do a bar chart.

Over the years, excel has made it easier and quicker to invoke a chart. Here's how easy it is. Drag you mouse over the names of the sales people including the column header, Sales person. Do not include total info. You have selected a range, A3 through A9. Now, depressing your control key, extend the range by dragging your mouse over the salaries. Again include the column header by do not include the total. This is the extended range I alluded to previously and it is only with this type of charting that we will support it. Now, at the moment, A3 through and E3 through E9 have been selected and you should be able to see thiso nthe spreadsheet. Now, click the insert tab of the ribbon and hone in on the middle section of graphs. Click column and choose whichever "sub graph" you want to produce the chart. Below is a composite of this.

Today, Let's start with some concepts in databases which will be useful in our study of Access and Excel. How did info processing work before the days of computers and then what was added as computers became available.

Now, for a one-dimentional approach to this problem, let's take a very simplified problerm and look at what tab operators would do 90 years ago to resolve this problem. Below is our accounts for a hyposthetical bank:

NY      200     M
NY      300     F
NJ      100     M
NJ      200     F
NJ      300     M
PA      300     F   
PA      100     M

We start the process by sorting on sex. We have two possibilities, ascending or descending. If ascending, we see these accounts as F to M. If descending, M to F. Least line of resistance is ascending. When sorting, different algorithms are used to sort and collate info together. You have no guarantee of positioning beyond what you ask. In this case, if we were to use Excel's sorting, the only thing guaranteed is that the F are together as are the M's.

Whatever algorithm we are using, below is what we wind up with as we attempt to congregate Fs and Ms

NY      300     F
NJ      200     F
PA      300     F
NY      200     M
NJ      100     M
NJ      300     M
PA      100     M

Now, whatever technique or software that would be used, it would work this way. There would be two accumulations, one for subtotal, the other for grandtotal. At a break in sex - break indicating change or end of data - the subtotal would be printed and the subtotal counter set back to 0.

                        Subtotal counter    Grandtotal Counter  Print
NY      300     F           300                 300
NJ      200     F           500                 500
PA      300     F           800                 800
                    Break     0                         F       800
NY      200     M           200                1000          
NJ      100     M           300                1100 
NJ      300     M           600                1400 
PA      100     M           700                1500
                    Break(Eof)0                1500     M       700
                                                  0     Total  1500

Let's look at this by state instead of sex. Below is our accounts:

NY      200     M
NY      300     F
NJ      100     M
NJ      200     F
NJ      300     M
PA      300     F   
PA      100     M

We start the process by sorting ascending on state.

NJ      100     M
NJ      200     F
NJ      300     M
NY      300     F
NY      200     M
PA      300     F
PA      100     M

Again, whatever technique or software that would be used, it would work this way. There would be two accumulations, one for subtotal, the other for grandtotal. At a break in state causes a subtotal.

                        Subtotal counter    Grandtotal Counter  Print
NJ      100     M           100                     100
NJ      200     F           300                     300
NJ      300     M           600                     600
                    Break     0                             NJ 600
NY      300     F           300                     900
NY      200     M           500                    1100
                    Break     0                             NY 500
PA      300     F           300                    1400 
PA      100     M           400                    1500
                    Break     0                             PA 400 
                    Break                                                  0     Total  1500

This cross tabulation is what pivot tables are supposed to replace. You will remember that we looked at the pivot table view in the hurricane problem done on Tuesday.

We mention the above as we are looking at the use of queries. Queries can do lists, filters, totals and joins. We'll work our way into this. I have an access file with two tables that you can access by clicking here.

We are going to use this access database to discuss queries. Below, we show a query being started.

We continue by selecting the aircraft table and asking questions of the table (and database). Can you guess what the question is?

Let's run this query. Below we can see the results but other things are taking place. We also see our first example of SQL the language of relational databases.

Let's visit the SQL area to see theexpression ourselves. if you know SQL, you can make modifications here.

Below, we start to look at joins. We will discuss the joining of plane designation between the two tables: Aircraft and events of wwII.

While we are dealing with this database, let's get into the toughest part of database concepts: no intermixing of totals and details. To prove out the uniqueness necessary for relational deatabases, the designers were forced to curtail many of the practices that were regularly done at the time. One of these was to control what was shown above in the banking problem, subtotals. SQ:L handles this by creating a designation group by where the detail fields are indicated that will be subtotaled. Below is such an example using the aircraft table to determine production by the four countries. Note: Group by is a part of the total line displayed by hitting the totals icon.

Above we showed the popup menu associated with group by. In totals, if not dealing with group by, we have many of the aggregates we talked about when clicking totals in datasheet view. There are several entries that I have not used incuding first, last, and where although I'm pretty sure I have an idea of what first and last do. If we go far enough, we will use expression in the census problem.

What about SQL view. Here the sql statements are probably easier to set than in QBE. It is a relatively easy Select nation, sum(production) from aircraft group by nation. If you were in Sql view and wanted to break this down further by fighter vs bomber (type), you could easily do this by inserting type between nation and production and add type to group by. We would have select nation, type, sum(production) from aircraft group by nation,type

The easiest way to add a column in the grid is to highlight where it should show and click insert columns above in the design tab. Below, we've done that.

We still have to resolve the underlying problem of last class. What is the percentage of a state's population that works in military bases? You can access this database by clicking here.

Once we are done with our census problem, let's try one more problem. I have modified a problem we will do in excel as access. Load up the fleming database by clicking here. This is an access database with one table designated as fleming. Below is a picxture of the table.

We have some data on the fourth sheet of an excel spreadsheet. We want to bring this info into access as a table. We can access the excel spreadsheet, flemingx.xlsx, by clicking here.

For this data, there is no header line. Access will default to field0, field1, etc. Let's make some changes only using datasheet view. Below, we see changes to name of field and changes to length of field.

Below, you can see what this looks like when done. We've stayed in datasheet view although this, obviously, could have been done in design view.

Now, we want to link the two tables. Name of book (title in one, book in the other) is the obvious choice. In doing this link we are doing a many to one situation. Now, once the link is done, we want to determine for each book title, the year of publication (book table), amount of books distributed and returns (sums calculated through the fleming table) and a calculated sum - subtraction of the two totals. Below is how we set this up.

Here's the calculation results

For the Monday, Wednesday and Friday class, click here to load our database as we left it.

For the Thursday and Saturday classes. You did not do the powerpoint project that the MWF class did and I thought I would show you what you missed. This will take several minutes and then we are off to the study of Access and databases. click here to load and see this.

This week we start our discussions of Access, a relational database. Your instructor will attempt to explain what is a database and what we mean by relational database.

We are going to create a database and this database will start with one table only. Below, we show screen shots of the creation of this database and table.

We've taken this about as far as we can without moving into joins. We will come back to this problem but first let's look at the creation of a database from existing data. Look below at a table of the storms and hurricanes that have hit the US in history. Note:this is from a web site and has not been authenticated, but we can use it.

Year	Month	States	Safir-	Max	Max.   	Name
		Affected	Simpson 	Pressure	Winds
			Category 	(mb)	(kt)
 
1851	Jun	TX	1	974	80	-----
1851	Aug	FL	3	955	100	"Great Middle Florida"
1852	Aug	AL	3	961	100	"Great Mobile"
1852	Sep	FL	1	982	70	-----
1852	Oct	FL	2	965	90	"Middle Florida"
1853	Oct   	GA	1	965	70	-----
1854	Jun	TX	1	982	70	-----
1854	Sep	GA	3	950	100	"Great Carolina"
1854	Sep	TX	2	965	90	"Matagorda"
1855	Sep	LA	3	945	110	"Middle Gulf Shore"
1856	Aug	LA	4	934	130	"Last Island"
1856	Aug	FL	2	965	90	"Southeastern States"
1857	Sep   	NC	2	961	90	-----
1858	Sep	NY	1	976	80	"New England"
1859	Sep	AL	1	982	70	-----
1859	Oct	FL	1	974	80	-----
1860	Aug	LA	3	945	110	-----
1860 	Sep	LA	2	965	90	-----
1860	Oct	LA	2	965	90	-----
1861	Aug   	FL	1	978	70	"Key West"
1861	Sep	NC	1	985	70	"Equinoctial"
1861	Nov	NC	1	985	70	"Expedition"
1862 	None
1863	None
1864	None
1865	Sep	LA	2	965	90	"Sabine River"
1865	Oct	FL	2	969	90	-----
1866	Jul	TX	2	965	90	-----
1867	Jun	SC	1	985	70	-----
1867	Oct	LA	2	965	90	"Galveston"
1868	None
1869	Aug	TX	2	965	90	"Lower Texas Coast"
1869	Sep	LA	1	982	70	-----
1869	Sep	RI	3	963	100	"Eastern New England"
1869	Oct   	ME	2	965	90	"Saxby's Gale"
1870	Jul	AL	1	982	70	"Mobile"
1870	Oct   	FL	1	970	70	"Twin Key West (I)"
1870	Oct	FL	1	977	80	"Twin Key West (II)"
1871	Aug	FL	3	955	100	-----
1871	Aug	FL	2	965	90	-----
1871	Sep	FL	1	982	70	-----
1872 	None
1873	Sep	FL	1	982	70	-----
1873	Oct	FL	3	959	100	-----
1874	Sep	FL	1	981	80	-----
1875	Sep	TX	3	955	100	-----
1876	Sep	NC	1	980	80	-----
1876	Oct	FL	2	973	90	-----
1877	Sep	LA	1	982	70	-----
1877	Oct	FL	3	955	100	-----
1878	Sep	FL	2	970	90	-----
1878	Oct	NC	2	963	90	-----
1879	Aug	NC	3	971	100	-----
1879	Aug	TX	2	964	90	-----
1879	Sep	LA	3	945	110	-----
1880	Aug   	TX	3	931	110	-----
1880	Aug	FL	2	972	90	-----
1880	Sep	NC	1	987	70	-----
1880	Oct	FL	1	982	70	-----
1881	Aug	GA	2	970	90	-----
1881	Sep	NC	2	975	90	-----
1882	Sep	FL	3	949	110	-----
1882	Oct	FL	1	985	70	-----
1883	Sep	NC	2	965	90	-----
1884	None
1885	Aug	SC	2	970	90	-----
1886	Jun	TX	2	970	85	-----
1886	Jun	FL	2	970	85	-----
1886	Jun	FL	2	970	85	-----
1886	Jul	FL	1	982	70	-----
1886	Aug	TX	4	925	130	"Indianola"
1886	Sep   	TX	1	973	80	-----
1886	Oct	LA	3	950	105	-----
1887	Jul	FL	1	978	75	-----
1887	Aug   	NC	1	946	65	-----
1887	Sep	TX	1	973	75	-----
1887	Oct	LA	1	978	75	-----
1888	Jun	TX	1	982	70	-----
1888	Aug	FL	3	945	110	-----
1888	Sep   	MA	TS	985	55	-----
1888	Oct	FL	2	970	95	-----
1889	Sep	LA	1	982	70	-----
1890	None
1891	Jul	TX	1	974	80	-----
1891	Aug	FL	1	985	70	-----
1892	None
1893	Aug	NY	1	986	75	"Midnight Storm"
1893	Aug	GA	3	954	100	"Sea Islands"
1893	Sep	LA	2	970	85	-----
1893	Oct	LA	4	948	115	"Chenier Caminanda"
1893	Oct	SC	3	955	105	-----
1894	Sep	FL	2	975	90	-----
1894	Oct	FL	3	950	105	-----
1895	Aug   	TX	1	963	65	-----
1896	Jul	FL	2	970	85	-----
1896	Sep	RI	1	985	70	-----
1896	Sep	FL	3	960	110	-----
1897	Sep	LA	1	978	75	-----
1898	Aug	FL	1	982	70	-----
1898	Aug	GA	1	980	75	-----
1898	Oct	GA	4	938	115	-----
1899	Aug	FL	2	979	85	-----
1899	Aug	NC	3	945	105	-----
1899	Oct	NC	2	955	95	-----
1900	Sep	TX	4	936	120	"Galveston"
1901	Jul	NC	1	983	70	-----
1901	Aug	LA	1	973	75	-----
1902 	None
1903	Sep	FL	1	974	80	-----
1903	Sep	NJ	1	990	70	-----
1904	Sep	SC	1	985	70	-----
1904	Oct	FL	1	985	70	-----
1905	None
1906	Jun	FL	1	979	75	-----
1906	Sep	SC	1	977	80	-----
1906	Sep	MS	2	958	95	-----
1906	Oct	FL	3	953	105	-----
1907	None
1908	May   	NC	TS	989	55	-----
1908	Jul	NC	1	985	70	-----
1909	Jun	TX	2	972	85	-----
1909	Jul	TX	3	959	100	"Velasco"
1909	Aug   	TX	1	955	65	-----
1909	Sep	LA	3	952	105	"Grand Isle"
1909	Oct	FL	3	957	100	-----
1910	Sep	TX	2	965	90	-----
1910	Oct	FL	2	955	95	-----
1911 	Aug 	FL	1 	982 	70  	-----
1911 	Aug 	SC	2 	972 	85  	-----
1912 	Sep 	AL	1	986 	65  	-----
1912 	Oct 	TX	2 	970 	85  	-----
1913 	Jun 	TX	1 	986 	65  	-----
1913 	Sep 	NC	1 	976 	75  	-----
1913 	Oct 	SC	1 	989 	65  	-----
1914	None
1915	Aug	FL	1	990	65	-----
1915	Aug	TX	4	940	115	"Galveston"
1915	Sep	FL	1	982	80	-----
1915	Sep	LA	3	944	110	"New Orleans"
1916	Jul	MS	3	950	105	-----
1916	Jul	SC	2	960	95	-----
1916	Aug	TX	4	932	115	-----
1916	Oct	AL	2	970	95	-----
1917	Sep	FL	3	949	100	-----
1918	Aug	LA	3	955	105	-----
1918	Aug	NC	1	988	65	-----
1919	Sep	FL	4	927	130	-----
1920	Sep	LA	2	975	85	-----
1921	Jun	TX	1	980	80	-----
1921	Oct	FL	3	958	100	"Tampa Bay"
1922	None
1923	Oct	LA	1	983	70	-----
1924	Aug   	NC	1	963	65	-----
1924	Sep	FL	1	980	75	-----
1924	Oct	FL	1	975	80	-----
1925	None
1926	Jul	FL	2	967	90	-----
1926	Aug	LA	3	955	100	-----
1926	Sep	FL	4	930	125	"Great Miami"
1926	Oct   	FL	1	949	75	-----
1927	None
1928	Aug	FL	2	977	85	-----
1928	Sep	FL	4	929	125	"Lake Okeechobee"
1929	Jun	TX	1	982	80	-----
1929	Sep	FL	3	948	100	-----
1930	None
1931 	None
1932	Aug	TX 	4	935	130	"Freeport"
1932	Sep	AL	1	979	75	-----
1933	Jul 	TX        	1	975	80	-----
1933	Aug	NC	1	963	80	-----
1933	Sep	TX	3	940	110	-----
1933	Sep	FL	3	948	110	-----
1933	Sep  	NC	2	952	85	-----
1934	Jun	LA	2	966	85	-----
1934	Jul	TX	1	979	75	-----
1934	Sep  	NC	1               	975	65	-----
1935	Sep	FL	5	892	160	"Labor Day"
1935	Nov	FL	2	965	85	-----
1936	Jun	TX	1	987	-----	-----
1936	Jul	FL	3	964	-----	-----
1936	Sep	NC	2	-----	-----	-----
1937	None
1938	Aug	LA	1	985	-----	-----
1938	Sep	NY	3	946	-----	"Great New England"
1939	Aug	FL	1	985	-----	-----
1940	Aug	TX	2	972	-----	-----
1940	Aug	GA	2	970	-----	-----
1941	Sep	TX	3	958	-----	-----
1941	Oct	FL	2	975	-----	-----
1942	Aug	TX	1	992	-----	-----
1942	Aug	TX	3	950	-----	-----
1943	Jul	TX	2	969	-----	-----
1944	Aug	NC	1	990	-----	-----
1944	Sep	NC	3	947	-----	"Great Atlantic"
1944	Oct	FL	3	962	-----	-----
1945	Jun	FL	1	985	-----	-----
1945	Aug	TX	2	967	-----	-----
1945	Sep	FL	3	951	-----	-----
1946	Oct	FL	1	980	-----	-----
1947	Aug	TX	1	992	-----	-----
1947	Sep	FL	4	940	-----	-----
1947	Oct	GA	2	974	-----	-----
1948	Sep	LA	1	987	-----	-----
1948	Sep	FL	3	963	-----	-----
1948	Oct	FL	2	975	-----	-----
1949	Aug   	NC	1	980	-----	-----
1949	Aug	FL	3	954	-----	-----
1949	Oct	TX	2	972	-----	-----
1950	Aug	AL	1	980	-----	Baker
1950	Sep	FL	3	958	-----	Easy
1950	Oct	FL	3	955	-----	King
1951	None
1952	Aug	SC	1	985	-----	Able
1953	Aug	NC	1	987	-----	Barbara
1953	Sep	ME	1	-----	-----	Carol
1953	Sep	FL	1	985	-----	Florence
1954	Aug	NY	3	960	-----	Carol
1954	Sep	MA	3	954	-----	Edna
1954	Oct	SC	4	938	-----	Hazel
1955	Aug	NC	3	962	-----	Connie
1955	Aug	NC	1	987	-----	Diane
1955	Sep	NC	3	960	-----	Ione
1956	Sep	LA	2	975	-----	Flossy
1957	Jun	TX	4	945	----- 	Audrey
1958	Sep	NC	3	946	-----   	Helene
1959	Jul	SC	1	993	-----	Cindy
1959	Jul	TX	1	984	-----	Debra
1959	Sep	SC	3	950	-----	Gracie
1960	Sep	FL	4	930	-----	Donna
1960	Sep	MS	1	981	-----	Ethel
1961	Sep	TX	4	931	-----	Carla
1962	None
1963	Sep	TX	1	996	-----	Cindy
1964	Aug	FL	2	968	-----	Cleo
1964	Sep	FL	2	966	-----	Dora
1964	Oct	LA	3	950	-----	Hilda
1964	Oct	FL	2	974	-----	Isbell
1965	Sep	FL	3	948	-----	Betsy
1966	Jun	FL	2	982	-----	Alma
1966	Oct	FL	1	983	-----	Inez
1967	Sep	TX	3	950	-----	Beulah
1968	Oct	FL	2	977	-----	Gladys
1969	Aug	LA	5	909	-----	Camille
1969	Sep	ME	1	980	-----	Gerda
1970	Aug	TX	3	945	-----	Celia
1971	Sep	LA	2	978	-----	Edith
1971	Sep	TX	1	979	-----	Fern
1971	Sep	NC	1	995	-----	Ginger
1972	Jun	FL	1	980	-----	Agnes
1973	None
1974	Sep	LA	3	952	-----	Carmen
1975	Sep	FL	3	955	-----	Eloise
1976	Aug	NY	1	980	-----	Belle
1977	Sep	LA	1	995	-----	Babe
1978	None
1979	Jul	LA	1	986	-----	Bob
1979	Sep	FL	2	970	-----	David
1979	Sep	AL	3	946	-----	Frederic
1980	Aug	TX	3	945	100	Allen
1981	None
1982	None
1983	Aug	TX	3	962	100	Alicia
1984	Sep   	NC	2	949	95	Diana
1985	Jul	SC	1	1003	65	Bob
1985	Aug	LA	1	987	80	Danny
1985	Sep	AL	3	959	100	Elena
1985	Sep	NC	3	942	90	Gloria
1985	Oct	LA	1	971	75	Juan
1985	Nov	FL	2	967	85	Kate
1986	Jun	TX	1	990	75	Bonnie
1986	Aug	NC	1	990	65	Charley
1987	Oct	FL	1	993	65	Floyd
1988	Sep	LA	1	984	70	Florence
1989	Aug	TX	1	986	70	Chantal
1989	Sep	SC	4	934	120	Hugo
1989	Oct	TX	1	983	75	Jerry
1990	None
1991	Aug	RI	2	962	90	Bob
1992	Aug	FL	5	922	145	Andrew
1993	Aug  	NC	3	961	100	Emily
1994	None
1995	Aug	FL	2	973	85	Erin
1995	Oct	FL	3	942	100	Opal
1996	Jul	NC	2	974	90	Bertha
1996	Sep	NC	3	954	100	Fran
1997	Jul	LA	1	984	70	Danny
1998	Aug	NC	2	964	95	Bonnie
1998	Sep	FL	1	987	70	Earl
1998	Sep	FL	2	964	90	Georges
1999	Aug	TX	3	951	100	Bret
1999	Sep	NC	2	956	90	Floyd
1999	Oct 	FL	2	964	95	Irene
2000	None
2001	None
2002	Oct	LA	1	963	80	Lili
2003	Jul	TX	1	979	80	Claudette
2003	Sep	NC	2	957	90	Isabel
2004	Aug   	NC	1	972	70	Alex
2004	Aug	FL	4	941	130	Charley
2004	Aug	SC	1	985	65	Gaston
2004	Sep	FL	2	960	90	Frances
2004	Sep	AL	3	946	105	Ivan
2004	Sep	FL	3	950	105	Jeanne
2005	Jul	LA	1	991	65	Cindy
2005	Jul	FL	3	946	105	Dennis
2005	Aug	FL	3	920	110	Katrina
2005	Sep 	NC	1	982	65	Ophelia
2005	Sep	FL	3	937	100	Rita
2005	Oct	FL	3	950	105	Wilma  
2007	Sep	TX	1	985	80	Humberto         
2008	Jul	TX	1	967	75	Dolly
2008	Sep	LA	2	954	90	Gustav
2008	Sep	TX	2	950	95	Ike
2009	None
2010	None
2011	Aug	NC	1	952	75	Irene

Now, we can access this as a text file by clicking here. But, just in looking at the data, let's make some observations per this data. In both Access and Excel, we would like consistency per the columns. Access demands this. For Excel, it would be nice. Do you see any points of inconsistency.

How about the whole table. Certain years indicate none. Those records have completely inconsistent information. How about the years from 1936 through 1980 where wind speeds are not entered for some reason.

Right here we have a problem per Access. Access demands consistency per data in a column (and consider that the first three rows indicate column headers). Columns can be numeric and there are various types of numbers allowed including integers and floating points, which we will explain. Automatic numbering is another possible type of column possibility. Text (also designated as string) is another type of column and something designated as Yes/NO (or True/False) is another. Notice that wind speed violates this need for consistency.

How about Hurricane name? ---- is as text oriented as 'lower texas coast' so this is not a problem. Buit, take a look at the Safir-Simpson category. TS (for tropical storm) cannot work with the numbers 1 through 5.

How will Access handle inconsistancies in data within a column. In the case of numerics, it sets everything to text. the number 65 can be entered as a number or text. If data is set as text, whether looking like numbers or not, numerical calculations cannot be done on the data. In class we will modify TS to represent 0 and use 0 in windspeed to represent -----.

Another need of Access which Excel doesn't need, is uniqueness. Each record must have a unique element to it as compared to another record. Do we have that here? Yes and no. It is probable that up to 1950 there was a difference somewhere is records whether by year, safir classification or pressure. After 1950 uniqueness is achieved through name and year. However, Access has the ability to assure uniqueness though automatic numbering which we will use.

Now, let's open Access. Access data file is designated as a database. A database can have a series of objects associated with it. One type of object is a table. A table is composed of a series of records having consistent columns. This is what we are talking about per this data, is it not.

Databases also have allowance for filtering and a cousin of filtering, queries. Filtering takes a table and shows results based on constrints. This is the and/or discussion of the librarians which makes sense since they were, in essence, discussing data bases. A query is a formalized way of filtering with other possibilities.

Report and input functions also exist within a database designated as report and form. We will deal with form almost assuredy during our classes but I have questions whether we will have the time to look at reports in this class.

To start this process, we need to modify this text file. delete those years with none. let's change TS in the category to 0 and ---- is wind speed to 0.

Let's open Access. The first page pertains to databases already in existance. We are interested in creating a database. Just hit the create button. Once in this new database, designated as databasex.accdb, click on external data and start the process of bring in this text file as a table. We can start this as indicated below.

Once you have your table within the database, the comsistency of the fields (columns) can be changed in one of several ways. Either through the fields tab as shown below or through the change of view. Below, we see a start to changing field type either through more fields or data type

The image below looks at the month field. Clicking the control on the field header gives you some possibilities. These options are brought to you by MS office since this is beyond what is required by a relational database. So, within the datasheet view you can saort and filter. Notice in terms of filter, all the unique entries are displayed.

However, most Access users either though the need for more control or habit utilize the design view. Here we have two sections of control. Each field allows for type and then when the type is chosen a bottom section allows for choices pertaining to a specific type.

The image below shows the bottom section when text is chosen. Several of the bottom section options should be discussed. Obviously length is important as the default for this for any field is 255. if you need 3 characters to represent a month, 255 is wasting 252 characters per record. For a small datafile this may not be significant but when holding billions of records, it is quite significant just per a question of response and speed.

In addition, on all of this a default value is allowed. If records are being entered at all times, this makes it easier for the input operator not to mention having less errors.

Below that is the validation rule and validation text. The text is displayed if the rule is violated. Now, how do you handle a validation rule. Not much different that what the librarian would say. Remember the or statement discussed, We could handle this with a ="jan" or "feb" or "mar" or "apr" or "may" or "june" or "jul" or "aug" or "sep" or "oct" or "nov" or "dec". The equal sign is not required in Access (it is in Excel) and the or's allow for a variety of answers but keeping wrong answers from being entered. Another example would be ="M" or "F" for a field that required an answer to gender.

This screen shows the lower content when dealing with numerics. Computer architecture governs field size and one has to determine the highest and lowest value one will encounter. Byte handles the values of 0 through 255 and only integer. Integer handles -32K through 32K with no decimal places. Long integer is -1 billion through 1 billion with no decimal places. Single use 4 bytes to provide for 7 significant digits with a floating decimal place and scientific notation. Double uses 8 bytes with a floating decimal place and scientific notation.

We should also add some content per the required option. If indicated as yes, you must enter something in the field before Access will allow you to move to the next record.

In converting from text to the table, we allowed this table to be indexed by an autonumber. This is the first column. Using integers we can have up to 2 buillion records processed through this table. Keep in mind that any number can only be used once. Therefore, you may not have 2 billion records at any one time since deletions count.

Let's return to datasheet view. Records are entered one at a time and are set premanently in what could call "entered order" Sorting occurs through something called indexes. Let's do an example of this looking at pressure. If we sort in either ascending order or descending order, we are not changing the entered order of the records. We are changing the order of display through an index. let's try some sorts. Bu the way, how would you get back to the entered order per display?

We can also filter. Filter is a term in data processing pertaining to the constriction of records shown. This is easiest shown by filtering on month. Notice how we can display the records of just one month or any specific months.

So far we have been looking a one table at a time. We have been dealing with Access as if it is a flat file database. Access can do much more. But to do this, we need to study queries. We will bring up a query using one of the tablew we already have dealt with and deal with one table. This should take us to the end of the class but next class we start to look at two (or more tables) in what is called joins.

Above, we moved data from a txt file to an Access table. It is easier to do this using an excel spreadsheet. click here to access an excel spreadsheet on German Uboat losses during WWII. The information on this is in sheet1. Create an access database with this info as an access table.

For the MWF classes we have a powerpoint in class/take home assignment designated as 'auric in pittsburgh'

It goes for 10 slides but we will show you the first 6 and probably do 4 at most in class.

The timings of these slides are as follows:

The music is by a Finnish/Iranian artist, Arash and you can access it by clicking here.

The colors are red and gold, the colors of the House of Pitt. Pitt was prime minister at the time of Forbes victory and it is in his name that Ft Pitt is named and therefore Pittsburgh is named.

You can access this powerpoint project by clicking here.

For the MWF class, click here to access the file as we left it.

For the Thursday and Saturday class, time restraints require a change from the MWF class. We will use a variation of the James Bond at 60 problem. Look below

To start the lecture, I would like to revisit the slide done before the break. click here to bring this up.

We want to concentrate on the following: timing of animations. We will try to see the difference between with previous and after previous. How do delays come into play. Let's take a look at this.

We would like to manipulate the picture of Fleming and Bond together. Let's first look at flying the picture in halfway between the fades. Now, let's add shapes - in this case arrows - pointing to James Bond and pointing to Ian Fleming. We can play with this a bit.

Finally, let's deal the bullets. click here for an appropriate bullet. In doing so we will have use of the ruler.

Having studied the ornithologist, we might as well continue by studying the more famous namesake. Click here for a powerpoint slide show.

For our final we want to crate a five(5) slide powerpoint presentation. Below is the 5 slides

If you run the script, you will notice quite a bit of animation. We will try to show this to you. In the first screen there is also synchronization between the appearance of the pictures and the various paragraphs. We also have a delayed appearance of a swivel tied into the appearnace of the last paragraph.

The entire first paragraph is displayed for 34 seconds. You will notice that this fits the music. The second paragraph shows after 6.8 seconds. The swivel of the James Bond at 50 text box occurs at 18 seconds and executes for 2 seconds and then the 3rd paragraph is displayed. To the right we show the animation panel of the first screen.

Per screen timings, I used 20 seconds for screen 2, 12 seconds for screen 3, 11 seconds for screen 4 and 24 seconds for screen 5.

Several cycles are used in this presentation. This is part of smart art. In addition there is a pyramid and an upward arrow. The cycle in screen 2 starts with a grow and turn. Two seconds before the screen ends the cycle spins. In screen 3, the pictures and the 007 are separated by a second each. At the end, the 007 disappears. If we have timed the music right, the cadense of the music should appear to be followed. Note: The numbers are word art at 196 points.

Screen 4 is in essence screen 1. Synchronized paragraphs and pictures are spaced 2 seconds apart. At the end, the upward arrow is displayed.

In screen 5 the bottom 2 text boxes appear 5 seconds before the screen ends.

The following are the files needed to create this. These are not exactly the pictures used in our examples but they will work.

007logo,gif
astonmartin.jpg
brosnan.jpg
daniel craig.jpg
drno7.jpg
fleming.gif
gunbarrel.jpg
jb1.jpg
jb2.jpg
Ursula Andress
laser.jpg
Connery
timothydalton.jpg
James Bond, the ornithologist
The James Bond Theme
Birds Of The West Indies Cover

You can see the entire powerpoint presentation in the Oct 18th lecture and if we have time we will add the James Bond theme as background.

And we are onto Powerpoint.

We will be discussing the ribbon structure and then beginning layout which includes a discussion on smart boxes.

We are aiming to a powerpoint presentation which includes animations. You can access this powerpoint presentation by clicking here.

To do this powerpoint presentation we need 3 pictures which you can access below

Feb 27 is the last day of Word for the MWF classes. click here to load an approximate representation oif where we left the document. I would like to show sources and table of index. If we have time we will look at table of contents. We finish up with comments, track changes and reviewing.

For the two classes this Monday, Feb 25, I have set the document almost to what we ended up with on Friday. THis includes footnotes and endnotes. But, I pushed ahead and showed screenshot. I think we will go over that again and in the process show table of figures again. After that, we will do sources for a bibliography and set table of contents and index. After that we want to handle accept and reject revisions). If we have time I would like to revisit bullets and numbering. We can load this document by clicking here.

For the Saturday class, we start with this click.

click here to pick up the document as we left it on Wednesday.

for most of you, in class last week you were introduced to the long document we will study in terms of Word. This article is on all things Goldfinger and you can access this as we left it by clicking here. We need to make some changes to margin and orientation. If you put on the ruler, you will notice that there is almost 10 inches of usable space which is much to long. Of course, the page length (using the vertical ruler) is much to short. Clicking orientation on the page layout tab indicates why - we are not in portrait.

Your laser printer doesn't care, quite frankly. It is designed to provide printing services way in excess to the two options that Word provides. Your laser can in essence print pages at any degree, not just the 0 and 90 degrees indicated here

Once we set portrait, we need to correct margins. In the olden days, using typewriters. it wasn't unusual to have margins like this. Today, the norm is 1 inch around (or 1.25,1.25,1,1 in prior to 2007). Below, you can see the differences as we change margins including the number of pages.

With 12 pages. we need a way to navigate through the document. The easiest way is through ctrl home and ctrl end. The scroll bar moves you sequentially through the document How about going directly somewhere. This breaks down into goto or bookmark. Goto is found on the find/replace/goto dialog box accessible in several ways. Once on here, you can navigate this document using page no, line no, etc. Use of + or - increments your movement. Below is an example of this.

Another way of movement would be by direct gotos to an area. This is a bookmark which we will demonstrate in class. Hit ctrl end and move to the end of the document. Let's assume that we will be doing more research on prious' and Toyota. Let's set a bookmark at the end of page 10 designated prius so that once we have our info, we can add to this document. Below, we show the two ways to access this bookmark.

A more modern way of navigation is through find. THis has changed since 2007 with a type of internet search built in. Thge locations of the text are displayed in the panel to the left and clicks bring you to the text.

We need to add a hyperlink and we will modify the document properties as indicated in the picture below.

You will notice that Word is defaulted to represent what it thinks is spelling mistakes in jagged red lines and grammar mistakes in jagged green lines. THis comes out of the dictionary that is a part of Microsoft office. You can modify this by adding entries into a custom dictionary which we will do. Obviously with a document that claims to cover all things Goldfinger, the word Goldfinger appears quite often and with a jagged red line associated with it. Let's add the word, Goldfinger, to the custom dictionary and see what happens. This is indicated below.

Let's ask the system to recheck the spelling and we should have cleared Goldfinger from assumed misspellings.

There are two ways to deal with misspellings. One is through the spell checker but another option is autocorrect. let's deal with the spellchecker first.

Generally, you run this using the entire document. Each word is checked per the system and custom dictionary. And, the system uses an algorithm to give you alternatives in case of a word not found. Ypu can take such a word and add it to the custom dictionary while in this process if you like. Or, you have options to have the system ignore this misspelling for the duration of the document. Since this document is influenced by British english, we should see multiple misspelling based on the differences between American and British English and, of course, proper names will also show up as misspellings. When I ran the spell checker (off of the review tab), the rfirst error I got pertained to a common abrev for Doctor. The Dr was flagged as an error. I inserted a dot after Dr and asked for change and went onto the next error. The name Drax is not so easily fixed given that it is a proper name. I could add it to the custom dictionary but it is easier to ignore it throughout the document. I am also offered possible corrections. We can see this below.

We will run though a series of pages on this which is somewhat tedious. I want to get to a green jagged line and we start to see rthis by page 4. Grammar is dealt with by rules and you can see this at that point.

Now, I have a question for you. How good is the grammar checker? IBM first, and then Microsoft have been working on this as are other vendors since the early '90s. The spell checker goes back into the '80s in Word Perfect. I believe the grammar chacker makes its appearance in 1994. But how good is it. Let's see by use of another word document. Click here to load a word document I call spell. Beside dealing with the spell and grammar checker, this is the first time we are using something called multiple document interface which allows Word to handle more than one document at a time.

What can you say about the spell checker/grammar checker using this document?

I want to modify the goldfinger document at the bottom. We'd like to add "at the end of Febuary" after transmission problems. But, you'll notice that we have misspelled the name of the month. This is a common mispelling for me and I can correct for this using autocorrect as indicated below.

We would like to add some pictures into this document. Here are the pictures listed below. Put them in your picture library.

We are going to load up Ian Fleming.jpg on page one. Remember this is brought up in line with text. Let's use position to place the document as indicated below.

Once our pictures are placed, we want to add in captions. After our captions we will do a table of figures.

Let's insert footnotes and convert these to endnotes. Footnotes are another way of citing references although they are most used for allowing an easier read of a document. Find the first instance of Crosse [&] Blackwell. Let's indicate in a footnote that this is a division of J.M. Smucker.

U\You can convert footnotes to endnotes. Endnotes are nothing more than footnotes that are placed at the end of the document. To start this process, move your cursor over any footnote and click your right button and select note options.

Below, we show the end notes as they are placed. Notice that footnotes are numbered, endnotes are numbered by roman numerals.

Let's work on text boxes. There are several ways to do this. In the catacomb problem we did traditional textboxes. There are two other ways that we can experiment with. One is a pulled quote which creates a text box around a selected text. We do it and then undo it. Another is a textbox template as found in the list of textboxes. let's do one that changes the direction of the text. Below we see the selection.

In the template created, copy in the text

Let's look at the use of headers. Again, we will use the templates. Let's deal with austere. This shoukd pick up some of our properties. Once done, and with a few more modifications to the document, we will try to create first, odd and even headers and footers.

Now, Let's do a table of contents. The paragraphs set in 14 points would be perfect for this. Microsoft has changed the way table of contents are set and created with almost every new version of word. This version seems to go back to the 2001 style. Those paragraphs with heading 1 seem to be chapter headings, heading 2 sets sub shapter headings. Let's set these.

To invoke the table of contents, create a new first page and place your cursor in this first page using ctrl home, ctrl enter; ctrl home. In references, click table of contents.

On all these tables that we create, keep in mind the user must refresh when pages change.

Let's set a table of index. I will show you how in class using mark index on the references page. In do this we will have two dialog boxes in action at the same time and communicating with each other.

One other table to consider is the citations. We will cite my article on the diskcoordinator and the book by Nigel Warburton published in 2003 by Routledge Press of London titled Enro Goldfinger: The life ofd an architect.

Not shown on our final goldfinger document for today is cover pages and hopefully we can look at this at the end of class.

I have added a list of the Fleming books. Highlight the list. click on bullet. This is the generic bullet. You could also number. Click on the command next to the bullet command. Now you see generic numbers. Bullets and numbers can be enhanced. Notice the added conrtrol to the right of the command. Clicking this gives more options.

What if we would like to place a picture as a bullet. Click here for a suitable picture.

While we are dealing with paragraphs, lets look at the indent button. This moves each line of a paragraph in a half inch. Use the outdent button to bring the lines back. We'll show this in class.

Your instructor is interested in breaking up documents intuitively per the user. Another possibility is drop cap. This creates a letter that is several times larger than normal placed at the beginning of a paragraph.

Let's look at a cover page. THis is new to Word 2007. It picks up a lot of the meta data in the properties section. Cover pages is a set of templates in quick parts. Since we have used austere in headers and footers, let's use Austere for a cover page template. You can select it as shown below.

Let's set a watermark. Watermarks used to be physical indents on paper but those days are long gone. We can set a watermark either horizontally or diagonally. Let's put in the verbiage "For educational purposes only". Watermark is an example of z-order and transparancy.

Let's look at several options per the review tab. You can see the comment group. Comments are messages to readers and are not printed. Below, we set a comment about the use of the word thrillers. This comment is displayed as a balloon.

For the Monday morning class we left the document at this point.. For the afternoon class, let's start here.

For the Saturday class, we have the Library lecture today in the AM. This is followed by the Library quiz which counts toward your grade so be there. In the PM we will continue with the Catacomb file as we left it last week. Click here to load this document.

click here for the picture of Michelle Pfeiffer as catwoman

click here for additional text

For the Frday classes. We have the library lectures today which are followed by the library test. This test counts per your grade so make sure to be there.

For today, we have in the morning a discussion and lecture by the reference librarian at WERC, Andi Sibley. At the end of the lecture a quiz is administered and, if you check your syllabus, this quiz counts in your mark for this class.

After break, we hope to finish the Catacomb problem. You can access where we were at the end of last week by clicking here.

We need to work on the coupon which is nothing more than a text box and we need to deal with the second page. To access the data for the second page click here. Below, in the Feb 6th lecture, is a greater discussion of this

At the end of our work with Catacombs, we will be dealing with a much longer document. I have several to choose from, but in Fall'12, I used a new document at that time and I think this term we will use this also.

The document pertains to a book that I started to read in 2011 about a famous British (originally from Hungary) architect, Enro Goldfinger. I found Goldfinger to be an interesting name per some of my other interests. One is my new (now almost a year old) gold Prius. It is named for another Goldfinger, although fictional, named Auric Goldfinger. Since Enro, Auric and my Prius are involved in this, I have titled this document, 'All Things Goldfinger'.

I can also point you to some other sources pertaining to the name Goldfinger. click here for a trip to Canada starring Auric Goldfinger, the car. And, you can click here for a discussion of Auric Goldfinger (and Enro, too, for that matter. Let's access our document and discuss navigation in large documents. We can access this file by clicking here.

For Feb 11th, please click here for the document as we left it

click here for the picture of Michelle Pfeiffer as catwoman

click here for additional text

Note for the NWRC class on Saturday, Feb 9th: Due to the storm I am aiming to start the class at 9:30. I have a bit of a drive from Chester County which will create the delay. If I am later than than that work on the myitlab tutorials. Do not do the tests.

Before we start the catacomb problem, let's look a final time at the spell and grammar checker. We can use the file that you find by clicking here.. I think the grammar checker leaves a lot to desire but keep in mind that this may be the most difficult programming problem ever attempted.

For the Mon/Wed/Fri class, click here to load the document as we left it.

The first chapter of the Word section in the book does the Aspen Falls Public Library problem. You should be doing the Aspen problem on www.myitlab.com. I believe I have loaded the necessary material from my side of this onto the course and let me know if this is not the case and we'll add more. At the end of the chapter are additional problems you might want to try and above I have provided access to the files needed for this although you can can find the files on the Q drive - a drive shared throughout the school - under Computer technologies and then cis103 student files. It is important to be keeping up with the book since your tests on the MYITLAB website involve the book contents.

As stated previously, We are going to do a different problem in class that is slightly harder and moves into the next chapters of the book in terms of tables, quick parts and objects for insertion. We could have used the Seuss file to do some of this, but this is a more complete problem.

I call this the catacomb(s) problem. Below is a part of what we are aiming at.

We do need some more definitions before we start a word processing problem. In the previous week we looked at the technology of the ribbon so you should be comfortable with that. You should understand character, word, sentence and paragraph. Thgis problem will hopefully give you more practice with character commands, not to mentioon paragraph commands. In the end, we should also see a use of styles. But, just to put you on a par with other users of Word, here are other things to consider:

We do word processing on a sheet of paper. Standard paper in the US is 8.5 inch by 11 inch. A second standard, designated as legal, is 8,5 by 14 inches. Years ago printers were designed to handle both standard although today you might purchase a printer that does not handle the legal standard.

Paper sizes are not uniform around the world and Word is designed to be able to handle these other standards although your printer may no be in that position).

When creating a word document, you already are aware that you skip space at the sides of the paper and this is called a margin designated as top, bottom, left and right. Top and bottom contain their own sections of code designated as header and footer.

Now, when entering data into the word, a single hit of the keyboard enters a character. A set of characters delimited by spaces, a beginning of a paragrah marker (to be defined) or end of paragraph (again t be defined) is a word. Word do not need to have meaning. 123 or abc are words when typed. To determine meaning and achieve corrections in spelling, Word provides tools such as the spell checker and autocorrect to accomplish this.

A group of words ending in punctuation is a sentence. Similarly, if a sentence should need more room than what is available left to right on the screen, the coding automatically moves to a next line as you are typing. This creates two definitions: word wrap and line. Word wrap (or a soft return) is where you are typing a word and it encroaces the right margin and the system forces you down below and to the right to begin a new line and line is the physical result.

The definitions above more or less meet language requirements. The next doesn't. A paragraph in Word processing is the hitting of the enter key. This creates, in essence, a user defined word wrap as the system takes you done and to the left. This is also known as a hard return. This definition of paragraph has great effect in Word as commands are, for the most part, broken down between character and paragraph. A character command requires text to be selected to invoke immediate results. If no text is selected, placeholders are inserted into the text at the point where the character command is initialed and these are invisible to you at that point. Later, if coding is encountered at the placeholders, the cosing is entered with these attributes. Paragraph commands require only that one or more charaters of a paragraph are selected (and in most cases you can get away with the cursor being positioned on the paragraph) to effect changes to the whole paragraph.

Word has a tool, as you are already aware, for determining where enters have been hit as well as spaces and this is hide show. Below is a graphic indicating this usage and we will use this tool as we do this problem.

In word processing several characters that are contiguous (together) separated by spaces or punctuation (or the beginning of a document or the end of a document or the beginning of a paragraph or end of a paragraph) is designated as a word. Any word can be selected by a double click on it. FYI: In XP and in Word 2003, there was something called a smart mouse which would automatically highlight the entire word one one of the characters was selected.

A group of words together before and after paragraph breaks (or beginning and end of doucument) is a paragraph. A paragraph break is nothing more than the hitting of the enter key. THius can be selected by a triple click and tyou can see the action as first the word is selected and then the paragraph.

Let's start with a new document. If in Word already, use file/new and select a blank template. If bringing up word without selecting an existing document, Word should put you into a new document.

Notice the title bar up top. The name should indicate Document1 - Microsoft Word. Word defaults to specific names for new possible documents. Documentx where x startts at 1 and increases for every new document added during a launch (launch is a Microsoft expression to designate that a version of word is up and running). You should be in document 1 at this time. You should not save the file with this name and even with the name doc1.docx that the system will substitute when you are in save as.

We have been in Word before. With a blank page, let's determine what Word sets as parameters. First, you should see a blinking indicator at 1 inch down and 1 inch across. This is the I cursor. The I cursor is where you enter text (and objects). Another cursor you should be aware of is the mouse cursor which is free to roam. If we take the serial and parallel discussions from previous classes, the I cursor os serial,the mouse cursor is direct. If I get the chance, I will show you a great new feature that has been added to the interaction of the I and mouse cursor but onward to other things.

2 new definitions. Where your mouse is atv the moment is the home postion of the document. You can always get to the home position by clicking Ctrl Home. As you enter data into the document, the end position of the document moves further down (at the moment with nothing in here, the end position is the same as the home position). You can always get to the end position by the cus of ctrl end.

What about the way this document will appear on the paper. It will not move from the top left of the paper to the bottom right. It is constrained by margins. Because of the nature if printers there is a natural margin of probably about a quarter of an inch. We obviously want to use more than this. Until 2001, Microsoft used the standard of 1" around. In 2001, they changed to 1.25" left and right and 1" top and bottom now known as the Word 2003 standard. For this version, they are back to 1" around. The top and bottom margins are special as they can contain additional information. Margins can be set through the page layout tab and can be changed at any time which means that the program will automatically realign the document as the margin is changed. Below, we show a composite picture dealing with margins.

Not that it means much, but you can also change the size of your paper in page layout under size. In the US there are two standards: 8.5 by 11 and 8.5 by 14. In other countries, however, there are other standards generally smaller than that of the US.

While in this section, we should also indicate that you can change the way the printer prints per orientation. Word (and Microsoft office) supports 2 way: portrait and landscape. Landscape makes you document look as it it has been shifted 90 degrees as it prints.

Let's start with an address block. We will show this to you two ways. One is by traditional paragraphs, the other using breaks. let's start with traditional paragraph ad put your hide/show on.

First paragraph is Mr. Bruce Wayne, second is 1000 Wayne Industry Drive, the third is Bat Cave, NC 28710. Your document should look like:

Mr. Bruce Wayne
1000 Wayne Industry Drive
Bat Cave, NC 28710

and you would see the end of paragraph markers at the end of each line.

We might as well use this for what I was supposed to do at the end of Monday's lecture, a discussion of cut.copy and paste.

To start this discussion, we need to give you even more definition. Word supports character and paragraph commands. In Character commands, you need to highlight those charaters that will be affected. In paragraph commands, you need only select a minimum of characters in the paragraphs you want to affect.

This breaks down into the following: The clipboard and font groups are character commands. The paragraph group is paragraph commands.

C ut, copy and paste therefore are character commands. You already know how to select a word or a paragraph with a double and triple click. How about several paragraphs. You can sweep your mouse through all the text of the paragrapghs. You can use the shift key in conjunction with the directional arrows to select the characters. Or, in this case, since we would be selecting everything in the document, we can use the shortcut of ctrl A. Ctrl A will select everything within a document.

Now, we are in the basis of cut,copy and paste. We might as well use copy. So, click copy in the clipboard group. You may have noticed that the copy icon was grayed out until we highlighted some text. Paste also was grayed out but as soon as the clipboard is activated by a copy or paste, the paste icon is activated.

Below we show this text coppied into several programs: notepad, excel and powerpoint. The clipboard is a system wide resource and can be used anywhere (and in any programs) that support text.

Let's get back to dealing with this document. Supposewe would like to see a different font or point size for this, how and what are we doing. Fonts are styles of characters and the world has plenty of these. You can see these by clicking the top left control in thsi group (it should read calibri) and moving your cursor over several of these selections as was done below. This is preview.

We should mention the use of Calibri. In the 1990 versions of Word the default font was Times Roman, a font created by the Times Of London. Fonts are copyrightable and the use of this font must be paid for. I assume, because of this, the Xp and 2003 versions of Word defaulted to Arial, a font developed by and owned by Microsoft as I understand it. Arial is more open than Timers Roman and probably this is what gave it it's name. Arial 10 was the height of Times Roman 12. For 2007 (and this version) Microsoft has defaulted to Calibri 11, another font owned by Microsoft. Calibri 11 is smaller than Arial 10 (and Times Roman 12).

As to point size, this references the size of the small a in 5terms of the number of periods (dots or points) put on top[ of each other to get to that size. As to displaying point size, it is similar to font and you can preview just as we did for the font. Below, we show this and you should keep in mind that you can preview but without selection the point size will return to what it was before the use of this control

Similarly, you can use preview in terms of underline. The U control in the font group will show a series of underlines when its downward arrow is pressed. Again highlight some text and move your cursor over the possible underlines to see what it will look like. Remember, unless you select, it returns to what it was. Similarly you can do the same thing with colors. In word, there is background and foreground colors,. Foreground id the ink, if you will, and background is the paper. Remeber one rule that every artist knows. A piece of r=text with the same color for background and foreground is invisible,

There are more font controls (and sooner or later we will go over these) but let's concentrate on another aspect of this document now. I need a salutation. After our address block, we want to add Dear Mr Wayne:. This brings up spme problems as to spacing as indicated in the problem in your book. We will discuss several of the possible solutions of your book but understand that we expec t to answer this question with another technique. But first, let's add the salutation. But where?

The traditional way of doing this is to go to the end of the document (the zip code) and hit enter keys as you work your way down the document. In this case, we probably would want to skip a line and then put in the salutation. B ut this version of Word has a better way of doing this. Move your mouse cursor to where you think you would like to add this text beyond the end of the document, Double click and you will find that the system adds blank line (enters, no text) down to your mouse position. In this case, its trivial, but when dealing with a heavy use of objects in addition to text, this technique can save a whole lot of time and trouble. Now, enter the salutation.

After the salutation we need to add another paragraph as indicated below:

Let me introduce myself. For years I was Batman’s rival, Catwoman. But, I have found a more purrfect way to earn a living.

Add this to you document and it should look like the following assuming that we have not changed font, point size, lack of underline and default coloring.

Now, let's discuss this paragraph you put in. Did you specifically create the break between the two lines. We hope not. If you did, you are defeating the entire purpose of this program. The program should have decided when to break based on a whole series of things such as font, point size, margins, etc. The program polls your typing. As you get close to a margin (in this case the right margin) it determines if the word you typed has violated (or encroached) the margin. If it has, it moves that word automatically down the paper to start the next line. We will be discussing spacing and you should understand that what has been defaulted as spacing (or what you have set) determines how far down this next line is. Any changes to any characteristics will force the program to look at your line again. If these new characteristics again have a word encroaching the margin, the line is reset, that word is brought down and it now leads the new line. On the other hand, if added spacing has been created, it will try to fit the first words on the next line up into the end of this line and if it succeeds, the document is completely layed out anew. The word processing industry calls this word wrap.

Now, let's look at spacing. There is spacing within the paragraph (between each line). spacing before a paragraph and spacing after. Quite frankly, this is one of the hardest things to control in Word. It is complicated by another factor, the difference in size between the default fonts of 2003 and before and the default font of this and the 2007 version.

Let's take a look at the spacing options. They are part of the paragraph group which should tell you how they can be controlled. Below, is what we would see when this control is pressed. In addition, you can use the dialog box that the group is associated with.

Most of the line options make sense except for 1.15. Can you guess what that might be used for. We'll discuss this in class as we end our lecture

If you look at the entire document, you are now at the paragraph: Introducing Catacomb, your one step location for Cat grooming. But, it certainly doesn't look like the other paragraphs. it is centered, bordered and shaded.

Your instructor has an interest in how one controls the reader's eyes and this is one trick. Human eyes are attracted to darkness surrounded by light. We will do this with this paragraph. It assures that any eyes that are set on this document will at first go here. The plan is to type in the next 2 paragraphs (introducing and you know how), center the introducing paragraph and then border and shade it. To add effect, we will increase the spacing between it and let me introduce and it and you know how.

Let's discuss justification first. But, to see the full effects, let's work on the You know how paragraph as it has several lines. We had a discussion about word wrap and the process of word wrap autimatically creates a jagged line at the right of the paragraph (assuming left justification). Left justification, the assumption that is automatically made, comes from the characteristics of english which is oriented from the top of the page with paragraphs as left as possible. Other languages (and societies) have other norms.

The picture below shows the 3 paragraphs left justified on the left and right justified on the right. The blue line shows the appropriate margin. The green boxes show the remaining space per line for each paragraph. It is the green boxesthat get manipulated during justification. From left justification to right, the boxes are moved from one side to the other. If we were to center, the green boxes would be split with a half on one side and the other half on the other.

In word, one more justification exits called either justification or full justification. This was made popular by Word perfect in the middle 80's. It is a technique that left justifies while also right justifiying. How can this be done. The green boxes are divided by the number of spaces in the line and added to each of those spaces. This occurs for every line but the last as it would be too obvious in that case. This technique will not work for documents where the average characters per word are high as gaps will be seen. Below, we set these paragraphs to full justification. We have taken the liberty to move lines. At the bottom next to each other is a line form the top paragraph above the bottom paragraph. Look at the spacing indicated by the black lines and circles. You should be able to make out a slight difference in the size of the spacing.

Now having done all this, we would like to center the paragraph starting with introducing. Any changes to the other paragraphs should be undone, You have a tool at the top in the quick access toolbar that is called undo. Another is redo. In word, (and this does not extend to Excel) you can go back and forth pretty much at will. Let's undo the justification changes done to these paragraphs. Below, you can see a composite of what was done. Notice that the undo gives you a detailed listing while redo indicated the next item to redo. The way this works is similar to the use of the navigation keys in browsers in the internet

Having done this, let's move to the paragraph starting with introducing. The technique being done could be accomplished with text boxes but we will use paragraph controls. Center this paragraph. Now, move the bottom right control in the paragraph group and click the right portion of the control to see the options. You can see that you can border various parts of the sides of this paragraph. In addition, although we will not use this here, you can set vertical lines which at times is very convenient. We want more, though. Click the last option, Borders and shading and it is here that we will set some aspects of this paragraph.

To the right are various schemes for this bordering. None is an option and disables these borders from being seen. It's the way to turn this off. I normally use shadow, the third scheme for this although you are welcome to experiment. To the right of the type is style color and width. We'll continue with black although feel free to change the color. The style is similar to the underline style we have looked at previously. Choose the first one, a solid line. Width in this case is important. Shadow will not be obvious for width less that 4.5 so click either 4.5 or 6 pts.

You should notice that the preview indicates this shadowing effect. You can deal with each side of the paragraph by clicking it to put it on or off. If you turn on of the sides off, the type of border will be set back to box.

Shading puts a little color (or a gray) into the box You can find this at the shading tab. If using black and white, set fill as no color. In the patterns you will find various possibilities. Ypur audience should determine what percentage to use. If geared to young eyes, head higher. if geared to older eyes, head lower. Keep in mind that your laser printer will probably give you a different effect than what you are seeing. Below, we show a composite of this for you.

Notice that we have the entire line from margin to margin bordered and shaded although the text is centered. To resolve this you can go into the paragraph dialog box and change the paragraph margins or use another technique. First, let's discuss the paragraph margins. This is really dealing with paragraph topography. In many years past, it was common to indent the first line of a paragraph and not space between paragraphs. There is at least one person in this class who learned composition this way. We'll let you decide who is this aged, ancient person among you. As computerization (word processing) made its mark in the 1980's, composition changed. You probably were taught not to indent but to space between paragraphs.

Paragraph topography tries to take this into consideration. Paragraphs are broken down between first lines and all other lines. Firstl ines can be indented or outdented (or as Microsoft calls it, first line and hang). Paragraph margins - called left and right indent - can be applied to all the lines of the paragraph. In the dialog box you see off of the paragraph group are controls to handle all these situations as indicated below.

This does seem like overkill to handle this. A better tool exists for this designated as the ruler. Prior to the 2007 version, the ruler was the default. In the 2007 and this version, the ruler starts out hidden. Youi can access the ruler by the control at the right above the right scroll bar or click view and click ruler in the show group. If set on and you get out of the program, the next time Word is launched the ruler will be active. If set off when you get out of the program, the ruler will not show the next time you launch Word.

What does the ruler give you. For any paragraph (or set of paragraphs that are selected) you can control first line or hang, right paragraph margin and the interaction on the left between the first line and the others. Your instructor will show you how this is done on another paragraph and then undo. For the paragraph in question, the omne that is bordered, we need to increase the left and right paragraph margins. Below we see this,

Before heading further down, let's work at the top of the document. Your navigation keys should make some sense now. Use Ctrl Home and you will move to the top of the document in what is designated as the home position of the document. use Ctrl end and you are moving to the end of the document. On any of the paragraphs, use end and you move to the end of that line. Use home and you move back. Sometime in the future we will be using a document that is more extensive and we will have need of page breaks. Move your cursor anywhere in this document and use ctrl enter and you will see a page break created at this point. This is alled a hard page break as you, the user, have created it. Page breaks instituted by the Word program are designated as soft page breaks. By the way, this answers why word wrap points are sometimes designated as soft returns and your enter key is sometimes called a hard return. use your undo to make the page break disappear. Use ctrl home to get to the top of the document.

We want to enter a date as the first line. Hit the enter key to move the address block down further. Now, click the insert tab and click date and time. Dates (and times) can be entered in 2 ways. One is hard wired so that the specific date picked always shows up. The other is by macro, so that the date is picked up from the system and inserted. The update automatically chaeck box handles this for you. The available formats come from the operating system and dates have designation of short, medium and long and you can see this as we have 10/3/11, 10/3/2011,Oct 3, 2011 and Monday, October 3, 2011. Pick the date format you want to show and double click it to insert it. I;ll let you decide whether this is a macro date or specific date.

We would like to handle the header. The 2007 and this version has really changed how Word looks at headers and footers. In a previous class we looked at a predefined header and how it took info from the document properties and used it. Everything is different including how to access the header. You can if you want click the insert tab and then header. It is easier to double click in the header location to invoke this.

When in the header the system helps you with a new tab, the header and footer tool. You can see in this tool a set of control that are of use for the header (and footer). Included are these preset header and footer objects. Date is included as is page number although both of these (you are already aware of date) are in the insert tab options. Access to pictures and clip are also are allowed although again you can use the insert tab of the ribbon for this. Notice that you can also clase the header and this bring you back to the document although it is easier to just double click within the body of the document.

In the Aspen Fall problem, there is a use of clip art in the body of the document. we are going to do the same but just place it into the header instead. We are going to complicate this procedure by intermingling the clip art with word art.

Now, what is word art. It is a cariculture of letters. Below, we track the process of putting a Wrord art object indicating catacomb into the header.

Not shown here is the invokation of an additional tab, designated as Word art tools. If on the word art object, word art tools will show up. if on the header, the header footer tools tab is in operation. Now, on the word art tools, let's look at this. You can space out or compress the letters. You can change the style. In addition you can change the color of the inside (known as a fill) and the outside (known as the border) of the characters. You can even change thei ntrinsic shape of the style although this style was chosen as it looks like a cat as it scratches (you have to be a cat lover to understand this). take a look at text wrapping. When we study picture we will delve into this and positioning more fully but for now, click text wrapping and choose tight. This will move the object somewhat. Move your cursor over calacomb and look for a 4 pointed arrow and when you see it, move the object with your mouse as the left button is depressed. Below, we have put this into position.

Now, back to the header footer tab. Click clip art and a panal will open to your right. Let;s find a cat and so enter cats as the search for. Nothing is more beautiful that jet black cats (in their opinion also) so let's look for a Halloween cat and you can see that I have selected this in the screen shot below.

All right. Similar to the previous object, you have a new tab associated with this clip art (although it will probably say picture). Enter the tab and move to wrap text and set this to tight. Now, move your cursor above one of the four handles at each ofg the corners. You should see a 45% grabber. Move this in till the cat is an eighth of the original size. Move your cursor over the cat un til you see a 4 cornered arrow. Depress your left button of the mouse and move the cat so that it straddles the second C of catacomb as indicated below.

On the agenda for today is finish the header. Not only will we do this, but can we in some way make this a template in the header section. We hope to prove this for you today.

If Salina likes this, can we make this header part of a template. This depends to some degree on your operating system. We are dealing with Windows 7 in class and will attempt to show this.

In class on Tuesday we distributed what this should look like when we are done. A variation of this can be seen by clicking here.

Look at the previous lectures this week to see examples of what we are doing today. At a certain point, we will need some text for page 2. We can access this text by clicking here.

We intend to create a style here. Sometimes this works well but sometimes not. We'll try as best as possible.

sooner or later we need a picture of Michelle Pfiefer as catwoman and you can get that by clicking here.

After doing the table, we will move to the header and then return for the picture and bottom text box.

If we are at this point, we are into the second page. The second page info is in a file. Before bring up the file, we need to create a second page. This is most easily done by using ctrl end to go to the bottom of the page and then clicking ctrl enter to create a page break. You can also clikc page break on the insert tab. Notice, as you create the new page that the header is automatically copied. This is what we mean by headers and footers. They are on all the pages (with some control) and you don't have to worry about placing them separately as pages are addded. And, if using page number, the numbers are automatically set for you.

We need to move this added document onto your socument section. click here to load this text. This is now the 3rd word document you launch of Word is dealing with. Npw, use save as to save the document to the document directory of your system.

Move back into the new page. Remember how we ran Excel through OLE. Microsoft has added another oprion under object: text from file which is added to the document. Let's invoke this and read in the file you just saved. Below shows my document after loading the addon text.

Now, this page looks relatively drab. We can do some things with it. Take the first offer, The classic day. Let's set this to Calibri 18, bolded and with 6 pts of space afterward, none before. We hope that wasn't hard given your participation in the class, but notice there are 6 more of these to do. Do we have to do each of these the same way. Isn't there some way to copy format just as there is a way to copy text. Yes, and there are really 2 ways. Let's work on the first way.

In the clipboard group is an icon designated a format painter. When clickng this, it is expected that your cursor at that point resides on something whose format you would like to copy. This is the paragraph, "A Classic Day", is it not. Move your cursor anywhere on this paragraph. When format painter is clicked, it tries to determine all the characteristics of the text and paragraph where the cursor was. Now, move your cursor over the document. Notice that the mouse icon has changed. It looks like a paintbrush. Highlight the text you want to reformat. When you let your mouse button go, these format changes will be applied to the text (and the paragraph). Let's try this for the grand escape. The grand escape text should look like the format of the classic day.

This works very well when there is a need for small changes. We have 6 or 7 of these paragraphs to change. What if we had 50? While better than setting each paragraph separately, it still would be a long process. For this we need styles.

You can see the already existing styles within the styles group. Originally, styles were paragraph oriented but in recent versions they have an indicator as to whether they should be paragraph, character or both. To see the charactieristics of a style, click your right mouse button over the style and select modify. Let's use the normal (or first) style. right clicking you can see that this is paragraph oriented and the preview tells you the characteristics of the style. Let's do the following. Using normal, let's create a new style we will call cis103. Below, we start this creation.

In addition, click the shortcut key. We ban use this to assign a special key set to activate this style. Use alt (alt is free throughout) and the letter c as we will name this style, cis103. Come back and save this selection. Move to the paragraphs in question. If done correctly, you should be able to move your cursor on a paragraph and click the cis103 style or to move to a paragraph and use ctrl C. Keep in mind, somethines the system gets confused. There is a clear formatting option on the style dropdown and you may need to apply this first before applying this new style.

Let's load up the seuss file by clicking here. The plan is to use three word files this term. Seuss will be the beginning file although in the past I have used it for very advanced subjects. Catacombs will bridge us to the final document which has yet to be decided. Catacombs is somewhat more advanced than the library problem in the book.

Seuss has been converted to Word 2007 and we know this by the extrension, docx - note we loaded seuss.docx. Docx is the file extension for those documents converted to 2007 or better. We can further break this down in the file tab. Notice the fourth command button as indicated in the lecture on Tuesday. Click this to convert this to the 2010 version.

Now, we are about to work our way to more serious files pertaining to Word and word processing in general. We will do this by stages. First, we move to an intermediate file where we can easily perform cut, copy and paste functions: look at headers and footers: deal with editing while we look at definitions such as word wrap, margins, justifications and the like. We can even use this file to get into spell checkers and grammar checkers. This small amount of code will also allow us to look at the new Word preview controls.

This "poem" can be found all over the internet. Other spoofs of Doctor Seuss can also be found. We are going to use this for a discussion of MS Word. First, how many paragraphs are there in this poem. Generally, students focus on the beginning title. Is this a paragraph or not? That's the wrong focus! The question is, what ends with an enter key? To confirm this, click hide show. A lot more paragraph symbols than was expected will show up. Count the paragraph symbols (except for null paragraphs) and you have your answer. You can verify this answer by looking at statistics at any time. Note: two straight returns (designated as hard returns) make a null paragraph which is not counted in statistics.

Now, students always ask, what's the big deal? Who cares if I end a line with an enter key as opposed to using word wrap. The answer! You are going around tools that word processor's provide you and in the end, with a complicated document, you will feel the effects. In the case of just margin changing, to get as low of an idea as possible, what we have here in this document that will defeat the use of margin changing to alter its layout.

And speaking of margins, what is the margins for this document? There are 4 margins: top, bottom, left and right. What is the norm for the world? What was Microsoft's norm in Office xp and 2003. What is the norm for office 2007?

Now, another question of a similar nature. Does this document have a header? Informally, the first paragraph acts as such. Officially, you should double click the top of the document (or click the insert tab and then header) and you will see something like what is below.

Above, you can see the header buffer which does not contain anything, least of all the text in question. This shot will also show the effects of hide show in the word processing buffer and you can see that every line has a paragraph symbol at its end. Notice also that the word processing buffer is grayed out and we should mention in class, and show,both header and footer and word processing buffer cannot be active at the same time. Why do you think this is the case?

Now, let's click back so that we are dealing with the document. We want to set this document so that we can use the word processing tools at our disposal. To do this, we need to delete the returns at the end of the lines except where we end a legitimate paragraph. You will notice as you delete these returns, the lines fill out as indicated below.

You should notice that there has been a change here. Using word wrap, more can be put on each line. Word wrapping is occurring at different spots. You should understand that even though this is supposedly a paperless society, there is still plenty of paper that we are dealing. Many document, including resumes, still need to be laid out so that they fit in one page or two pages, etc. While this does not apply to electronic resumes, you still need a paper based one. What happen when you are dealing with a paper based resume that is 1 page, 1 or 2 lines. This is not professional and use of margins can effect the change needed.

Let's work on a small situation. The word standard for margins was 1 inch around for word 97 and word 2000. A change occurred for Word XP and Word 2003 such that the margin was 1.25 inch left and right and 1 inch top and bottom. The seuss document is from the XP era and has 1.25 in margins left and right. Let's change that to the new norm for this wersion, 1 inch all around, margin changes are part of the page layout tab. Now before we do anything, notice that the first line ends at 'a very last' because of word wrap

Most of the rest of today's lecture discusses the use of two types of commands, Character and paragraph. An easy way to look at this is through the ribbon. Indicated below is the breakdown.

Now, we will get back to paragraph commands. Let's concentrate on character commands and add something. In previous versions, If one wanted to try something, you had to do and then undo it. In this version, most commands (especially character) has a preview option. This allows you to position your mouse cursor over a control and see what it would do. By moving your mouse off, the text returns to what it was. By clicking, the text is changed. We'll show this as we go over this.

One other factor to discuss. The system allows for placeholders. By that we mean that if no text is selected, you have only positioned your mouse, you can give commands that will go into effect as soon as text is entered at that point. I don't like this and would prefer not to be surprised when this is activated, but we'll show it to you just in case that is what you do in practice.

We should also add that extended mouse selections of text are allowed in character commands as in paragraph. In this case, only the text selected would be affected by the character command.

Let's start with font. There are more than enough in the world as you can see by clicking the font combo bax. Select some text and let's preview. What are the characteristics of a Font. The cartographer tries to come up with a theme and in doing so defines a dot or period also known as a point. The small a is used in this. It's height is determined by these points put on top of each other. Giving an example. Select some text and then apply 24 Times Roman. Select the next few words, use Ariel 20. The height is not that much different between the two selections. What is different. Serifs are in place for Times Roman while Arial is sans (non) serifed. Arial's letters are more open than Times Roman.

We had said that Ctrl A would select all the text. Let's do it. Now, use preview to look at this document in different fonts. Keep the points consistent. Notice that for the entire document, Times Roman 12 is shorter in length than Arial 12. Calibri 12 probably splits the difference. Lengths do change between fonts as was expected.

Now, take a look at any of the fonts and look at the point possibilities. They are specific. You probably will see 8,9,10,11,12,14,16,18,20,22,24,28,36, 48, 72. The A hat and little A hat (next to points) when clicked will change your selection up and down this scale. Let's try it. Select some text and click A hat and watch it go up. Reverse the process with the other command.

What we call fonts are font styles in reality. They give a template of how a character should look. Ther fonts are really how the character looks when normal, bolded, italized and with a combination of bolded/italicized. The file that gives the operating system font info breaks this down between these possibilities. A Bold A is a different character than a non bold A. Italicized characters have different characteristics, also

Underline is not a characteristic of fonts. These are mathematical representations that are applied to the character. You can see by clicking the downward arrow next to the U that there are many underlines to choose from. Did you know that it doesn't stop with underline. Stikethrough and double strikethrough are underlines that go through the middle of the character. There is such thing in publiching as an overline although we don't see it designated here (although borders will do this for you)

Speaking of strikethough, that is the next command and then there are two commands that you should use very rarely, subscript and superscript which I will explain with the admonistion that you should not really be using Word for mathematical editing.

Case is a term used to indicate capital letters versus small letters. With this there are five different cases. Caps, non Caps, title (where the first letter is capped for each word), sentence (where only the first letter of a sentence is capped) and reverse. below, we take 5 paragraphs and show these one by one.

Color in Word is text oriented and broken down between background and foreground. Foreground pertains to the lettering. Backgroud is the space that the lettering sits. The concept is pretty easy although you have to remember that anytime the foreground and background color are the same, the lettering is invisible.

We are on to paragraph commands. I did ask what you thought the fourth justification option was. Below, we put side by side this poem with and without this fourth justification.

Can you see above what is the difference. Full justification (Microsoft calls it justification) was created in the 1980's by Word perfect and became a fad in word processing. It's not that faddish anymore. It is a mathematical technique of increasing the size of spaces through the line. The last line of a paragraph is never affected.

We had talked when do our web script about ordered and unordered lists. In word processing unordered lists are designated as bullets, ordered lists as numbers. Bullets can be many symbols which we will go into and even a picture. To do a picture, let's load one using by clicking here. Save it to my pictures. We'll try to duplicate this picture here.

Select the last 3 paragraphs. Using the numbering icon, select define new number. Select the last numbering option and you will get these types of ordinals. Take paragraph 2 and 3, select the bullet icon, define new bullet, and use picture, Click import and aim to my pictures. There's the 007 sysmbol and when you click it you are setting the system up to use the picture as the bullet. Now, these are extreme bullets. We will discuss the normal usage and the normality is that numbers are bullets with some type of chronological impact.

Like many students, you will notice that I have not taught you to use the tab. Tab sets do exist in Word but really are old technology. In addition, indiscriminate use of tabbling can affect documents in a poor way. What you want to use is indenting and outdenting in general (although we will later go into paragraph topography). These are the two controls to the right of numbers. Now, below, we concentrate on one paragraph hitting the indent control several times and then the outdent to reverse the process, Notice how the paragraph is changed. An additional margin is imposed but specifically for this paragraph.

In a similar situation we can discuss the controls on the ruler. Last term I used the next document to document this, but timing considerations will lead me to deal with this at this point. This leads to definitions of paragraph topography.

let's look at the pluses and minuses of the spell checker, the grammar checker, the thesaurus and translate. First to translate. I believe that this is the way, once Word is established, to change to another language. This will be transferred to the internet and a microsoft service for translation. For those of you who know French (which does not include your instructor who is still trying to learn English). Use the seuss document to determine whether this translation is adequate

I did the translation at home and copied it to file. Load this file Que se passe.docx and translate it back to English.

Before returning to our discussion of spell checkers and the like, Let's discuss multiple documents. Word, and most of the office applications, use something multiple document interface. This makes Word act like your desktop and it is able to hold a lot of documents at the same time. At one time it was possible to bring up multiple copies of Word in your operating system. This caused your system to slow and sometimes crash. Today, Word is quite strict and generally all word documents are applied to the Word program that is running. You can see all the documents that Word contains using view. There are many options - quite a bit to get in trouble with. Best is switch windows. The document that you click is document you will see. There is view side by side which works well when only 2 documents are in play - not so well with more. Max out whe leaving this - cancelling out deletes that document from MS word. The next is arrange all which will try to fill the screen with all these documents working itself vertically as versus the side by side horizontal emphasis. See these examples below

Back to Spell Checkers. In the seuss file, notice the red and green wavy lines that are below the text in the document. Red indicates a possible spelling error. Green a possible grammar error. There are limitations however and your insturctor will show you such in another document designated as spell. Load spell.doc by clicking here. This may be a third document within Word. At home, try side by side and arrange all again and determine if these are acceptable solutions to looking at all these documents at one time?

On this spell document, how are we doing with spelling and grammar. Probably not well? Why? Your instructor, who is the last person in the world who should be taking about language will try to indicate to you why this fails.

If we have time today, we are onto tthe second Word problem. As stated previously, We are going to do a different problem in class that is slightly harder and moves into the next chapters of the book in terms of tables, quick parts and objects for insertion. We could have used the Seuss file to do some of this, but this is a more complete problem.

I call this the catacomb(s) problem. Below is a part of what we are aiming at.

First thing we will do today is formmail. This is the easiest way to make contact with your instructor. Above is the form and we will show you how to activate it and how it gets to me.

Today, we will be looking at Word and office. What are some common elements. Let's start with the look. You will see that the top of the screen is dominated by the ribbon. What does the ribbon do? to lok at this, let's look at Word 2003, the version before the change to the ribbon.

The top of this program is dominated by the Microsoft menu system. Notice how one drills down into the selection. You are looking at a very small laptop with a relatively small screen resolution. As options and enhancements were being considered for the new version of Word. it became apparent that this menu system could move the user off the screen. The answer to this is the ribbon whose technology is such that in one way or the other all options are displayed. The following four scrren shots show various screen resolutions and how the ribbon responds to this.

By the way, although this is best indicated in an operations course, screen resolution is resolved on the control panel. The various Microsoft operating systems show this at different locations of the control panel - in Windows 7 this would be appearance and personalization - as screen resolutions. You can manipulate pixels on the X and Y axis. If we have time I will discuss what these pixels represent as we discuss font and font characteristics. Below, is the screen resolution on a vista machine.

Now, dealing with the ribbon in whatever appearance, this structure breaks down into several sections. Each section is designated as a group and the group may have a dialog box associated with i for more advanced options. You click on a tab, and that tab's controls make up the ribbon at that point. Above the ribbon are added controls including the office button and the quick access bar. You can see this laid out below.

Now, let's continue with common elements. One is the ability to save what I call 'meta' data. In addition, various file manipulation options are avaiable. This is all out of the file tab which has been added for the 2010 version of office. Below, we see the file tab. To the left is various options to click. This screen is dominated by a list of files used previously in Word (or any of the office applications). This is called a pick list.

Clicking on info, you see other pices of information including properties. The info here involves dates when saved, etc. Below, we see this info per a new file. We'll discuss some of these control buttons and we'll continue by looking at document proerties.

Here's where meta information - information about the document - can be accessed. Let's click on this and see what we get. Below, we see this. This meta data has become more important over the years and, as we will see as the course developes, is used by many of the new features of Word.

Let's get back to the file tab. Indicated at hr bottom is options. What does this show. Various options exist in terms of the way Word operates and this includes manipulation of the ribbon and the quick access bar. There is an added tab that be applied on the ribbon which we will invoke. You should understand that most additions to the ribbon tabs are created by the context you find yourself in while operating the program. But, the developer's tab can be set on or off. This tab is exactly this - tools for those who program Word. We'll put it on and off to show you. In addition, we will attempt, if we have the time, to show you how to manipulate the quick access bar.

We still will have lectures but you need a file to work on. Let's load up the seuss file by clicking here. The plan is to use three word files this term. Seuss will be the beginning file although in the past I have used it for very advanced subjects. Catacombs will bridge us to the final document which has yet to be decided. Catacombs is somewhat more advanced than the library problem in the book.

Most of the rest of today's lecture discusses the use of two types of commands, Character and paragraph. An easy way to look at this is through the ribbon. Indicated below is the breakdown.

A major difference in terms of a character vs paragraph command is how to select text for use. In Character commands, any text that needs to be modified mut be selected. In paragraphs commands, only a small amount has to be selected of each paragraph or, in the case of just dealing with 1 paragraph, all you need is your cursor positioned on the paragraph,

To accomplish paragraph commands wisely, the system allows for advanced mouse functions. Follow you instructor as he uses the ctrl key to extend the dragging of the mouse. We'll use this in a very quick demo. Click on the second paragraph (the first paragraph with nultiple lines). Right justify it. Notice all we need to do is click. Use undo to set this back to where we were. Now, highlight the last word in paragraph 2 and the first in paragraph 3. Again right justify. See how both paragraphs are affected. Use undo again. Now, select a word in the first paragraph. Use the control key to select a word in the fourth paragraph. Do the same in selecting a word in the 6th paragraph. Now right justify. Below we show this.

We should also add that extended mouse selections of text are allowed in character commands as in paragraph. In this case, only the text selected would be affected by the character command.

We are on to paragraph commands. I did ask what you thought the fourth justification option was. Below, we put side by side this poem with and without this fourth justification.

Like many students, you will notice that I have not taught you to use the tab. Tab sets do exist in Word but really are old technology. In addition, indiscriminate use of tabbling can affect documents in a poor way. What you want to use is indenting and outdenting in general (although we will later go into paragraph topography). These are the two controls to the right of numbers. Now, below, we concentrate on one paragraph hitting the indent control several times and then the outdent to reverse the process, Notice how the paragraph is changed. A n additional margin is imposed but specifically for this paragraph.

In addition, although in previous classes I have used the catacombs problem to study the ruler, Let's use this file to show you what we can do with. This will involve the study of paragraph typography.

Some math used in excel:

Population of the country in 1990 = 247 million.
Assume the country expands 3 million per year.
What is the number of students in 1995

there are 12000 books distributed to a book store.
11000 are sold
1000 are not
The agreement between the book distributor and the book store is that each book sold is worth a dollar and each book returned is at a cost of 50 cents. How much profit does the book store make.

How are numbers and text represented in a computer from day 1.
1 or 0: this is a binary digit (bit)
in the old days, 6 bits represented a byte.
Since 1962, 8 bits represent a byte. This is known as hexadecimal.

What is a byte:
it can represent mathematically 0 to 255
It also is forced to represent characters (including numbers) Characters are not mathematics. We can see ascii charts at http://web.cs.mun.ca or www.asciitable.com
The character one is not a mathematical 1. If needed, one can create a program to convert this. If not. math on this level cannot be done.

Today in addition there a secondary situation designated as unicode which characters are represented by 16 bits or 2 bytes. If you deal with semitic, cyrillic, asian languages, this is what you are dealing with.
A computer math class (15 weeks) can explain to you in more and greater detail abut this. We will use this to define other things but first a bit more on math.

We left at one byte being the value of 0 to 255
We also are allowed 2 consecutive bytes which are 16 bits.

To deal with this we need some terminology:
1K = 1024 bytes
1meg = 1024 time 1024 bytes
1gig = 1024 times 1024 times 1024
1tera = 1024 times 1024 times 1024 time s 1024

16 bits represent -32K to 32K or 0 to 64K. This is known as a half word and in programming today this is called a short integer
We can also have 32 bits: This represents -2,147,483,648 through 2,147,483,647.
We can also have 64 bits: this represents -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

Notice there are no decimals: How is decimals handled. Similar to scientific notation: Remember 6.023 X 10^23, advogadro's number this is
602300000000000000000000 or so. Scientific notation allows for the movement of decimal points. What's constrained is the number of significant digits.

Scientific notation is replaced in computers by floating point decimal. One possibility is single floating point where 4 bytes ( one word) are split between sign, mantissa (the computer equivatent of power) and 6 to 7 digits.
In addition, the 64 bit equivalent of this (8 bytes) again has a sign, longer mantissa and 16 to 19 digits. This is known as double floating point.
In addition there is an 10 byte designation for this known as a long double floating point and the intel processor converts numbers to a double double floating point of 14 bytes.

The more bytes (bits) in the number, the higher the mantissa and the higher the precision.

What do we do this this. This is known part of what is known as computer architecture. Your computer's memory, known as RAM, is chock full of these. But we are interested in what happens when these are moved to media.
So. what is media:

Your main componebt of media is known as a hard drive or hard disk. 99.99% of the time this is the C drive on your machine. For the windows operating system, this is designated as c:. A second disk drive could be put on your machine ain which case it would be designated as D.
Another possibility of media is a CD or DVD. This media is not as flexible as your disk drives but are very useful i many ways.
Another source of media is network drives. These are dik drives that are remote from your machine on another machine which you have been given access to.

All this media has a kind of similar architecture. The industry would call this treelike with the root folder (directory) having leads into subfolders (or subdirectories). These subfolders can play the same role of leads into other subfolders. Each folder or directory has a name that you (or someone else) gives it and the path to it is these names delimited by slashes or backslasher. The root directory however is always designated as the disk drive such as c: or d:
Let's assume we want to get to the subfolder xyz of the folder abc which is linked to the root directory of rthe c drive. We woud be talking about c:\abc\xyz. In some cases the designation c:/abc/xyz would also be acceptable.

What populates these folders or directories be they the root directory or a directory such as c:\abc\xyz. The answer to this are the individula links between the hierarchies, shortcuts and files. Shortcuts are immediate links to file and therefore, what is a file and what types are there.
In class we will discuss the logical aspects of a file, but for our computer it is interested only in the physical aspects and that is a set of bytes that are all accessed by some designated name. What are the characteristics of a file name.

File names are divided into 2 parts, the name and the extension. The extension,, examples of which would be txt, doc, docx, xlsx, accdb, mdb, pps, indicate to you and the operating system what type of application this file is a part of. We now are a the point of indicating that such files are input ot application programs and you probably are aware of some of these such as Word, Excel, Powerpoint and Access. There are other types of files not associated with application. SOme of these are executable such as exe and com files. Others are computer hardware files such as sys, dll and inf. And others are associated with the operating system such as cur (and others)
The name is a set of characters that can be delimited by dots or underlines. so, names can be abc.def_ghi.jkl.doc \. In this example notice that there are several dots: the rule is that the last dot indicated the break between name and extension.

Names and extensions are important given that on an hierarchy there cannot be two files of the same name. Origianlly DOS/Windows would flag such a requestion. Later Windows would create a .1 or .2 if applicable. Today the operating system appends a "copy of" to the file name.

The names appear at several levels but especially when displaying hierarchies. How do you do this. Find the ocmputer icon on your desktop and click. You should see the list of media. Click into the media (we'll use the C drive) and you will see subfolders and files on this directory. You can just continue (as we will) to see this on several folder/direcotries. Your instructor will point out othrer things of interest such as shortcuts and hidden and system files. These hidden and system files should not be monkeyed with in general as the loss of these could affect operation of your machine. However, it is also possible that malware hides in the same way.
Now, let's ask this question of files again. What does a fiie contain. Bytes. Now, if all the bytes represent text, this is a text file designated with the txt extension. Netting this type of file, application files are designated a binary files for they contain bytes representing text with multiple bytes representing numbers and other byte strutures representing default values needed to run the application program.

Before we start to create a text file, let's talk about what is displayed per the information you see in the folder (directory) Each file has statistics per its length (size), creation and modification dates, type (what application if applicable), etc. In fact, we can invoke the system so that you see all the possible pieces of information attributable to a file. It is quite extensive. You can display any of these pieces and sort on them at will.
Every file extension has a set of options available to it that you can see by right clicking the name or the icon. And, speaking of icins, you can see that consistent icons are shown based on the extension of a file.

We are into our next problem. The major flaw of the problem is lack of detail info. We, in essence, had summary info for all the sales paeople. Life, and especially, data processing, is not like this anymore. You use excel today to break down detail info into summary info. The rest of our study of excel will be to do this. There will be three techniques. The first one, subtotals, we will do just one time to show you an older way of handling this. After that, it is dealing with pivot tables and pivoot tables through the new construct table which I think you will like.

Our first foray into this is one of my favorite problems as you can probably guess. We are talking about the books of Ian Fleming. There is no excel spreadsheet. We have to create the spreadsheet and there are 113 row (112 line items) of information. And, we have to work out some calculations. Calculations seem to be a problem for most students in this school. We will do this correctly in class but keep in mind that on the test, you do as best you can but you must make sure you finish the problem so that I can give you the highest amount of credit.

Onto our first problem dealing with detail info into summary components. Click here to load a Word document indicating the books of Ian Fleming. In all of these problems, it is important to read the problem carefully. In this one, the heirs would like to know the total profitability of the books and the profitability of each book. Notice that each book title occurs 7 times. We need to be able to sum up the profit of each book into a total.

First, what is the profitability per line item. At that time, books go to a book store. If sold, there is a price per book that goes back to the publisher (in this case the heirs). returns are not sales. And, it has been agreed that each book returned will cost the heirs 50 cents. In class we will indicate the formaula but first we must copy all the line items into excel and we will show you how to do this in class.

Because we are going to do this problem 3 ways, we will also loaded sheet2 and sheet3 with the entire table you saw in the word document. Please remember that there are 113 rows. The first row is the column header meaning that 112 of these rows are line items. There are 16 books and you will notice that they repeat 7 times each (16*7=112) and our assignment is to determine the profitability world wide for each book and worldwide for all the books.

Let's begin by renaming the sheets. Sheet1 will be designated as subtotal. Sheet2 will be designated as pivot table. Sheet3 as table construct. Now, move back to sheet2, Pivot table. For all the sheets, we start the same. We need to calculate the net profit per book title per book distribution point - each line item - and we can do this in the pivot table sheet. Our calculation should be (books distributed - books returned) * Gildrose royalty per book - books returned * .5. Set this up in this spreadsheet.

Now, for a one-dimentional approach to this problem, let's take a very simplified problem (this has been discussed before) and look at what tab operators would do 90 years ago to resolve this problem. Below is our accounts for a hyposthetical bank:

NY      200     M
NY      300     F
NJ      100     M
NJ      200     F
NJ      300     M
PA      300     F   
PA      100     M

Whatever algorithm we are using, below is what we wind up with as we attempt to congregate Fs and Ms

NY      300     F
NJ      200     F
PA      300     F
NY      200     M
NJ      100     M
NJ      300     M
PA      100     M

                        Subtotal counter    Grandtotal Counter  Print
NY      300     F           300                 300
NJ      200     F           500                 500
PA      300     F           800                 800
                    Break     0                         F       800
NY      200     M           200                1000          
NJ      100     M           300                1100 
NJ      300     M           600                1400 
PA      100     M           700                1500
                    Break(Eof)0                1500     M       700
                                                  0     Total  1500

Let's look at this by state instead of sex. Below is our accounts:

NY      200     M
NY      300     F
NJ      100     M
NJ      200     F
NJ      300     M
PA      300     F   
PA      100     M

We start the process by sorting ascending on state.

NJ      100     M
NJ      200     F
NJ      300     M
NY      300     F
NY      200     M
PA      300     F
PA      100     M

Again, whatever technique or software that would be used, it would work this way. There would be two accumulations, one for subtotal, the other for grandtotal. At a break in state causes a subtotal.

                        Subtotal counter    Grandtotal Counter  Print
NJ      100     M           100                     100
NJ      200     F           300                     300
NJ      300     M           600                     600
                    Break     0                             NJ 600
NY      300     F           300                     900
NY      200     M           500                    1100
                    Break     0                             NY 500
PA      300     F           300                    1400 
PA      100     M           400                    1500
                    Break     0                             PA 400 
                    Break                                                  0     Total  1500

Now, let's do the same for this problem of the books of Ian Fleming. We need to congregate all the casino royals together, all the goldfingers. We should sort on the book title. Now, one note. In a previous sort we used the full scale sort icon given that another line item was added for totals. We will not have to do that here. So, we can use the simple sort A to Z or Z to A that we have seen on the Data tab of the ribbon.

Now, let's copy this relationship down through all the line items. When you are done, your spreadsheet should look something like below.

I have given the new column a title of profit per book and set this new column to 2 decimal places

For the section 36 class, you can load where we left off on Wed. by Clicking here. We need to know the books distributed, the books returned and the profit for each book. At a break in the book, we need these subtotals. Wouldn't be so bad either if we could get a full total of all the books sold world wide.

A warning is necessary on this. Most students when doing this in a test, forget to do their sort. Or if sorting, use thec wrong column. You must think about the ultimate data you want. The Fleming heirs are asking the accountants for a breakdown by book title. It would be counterintuitive to assume that anything else in this spreadsheet should be sorted, for example, how would we resolve the Fleming's request if we sorted on continent. Continent would be viable if the Fleming's had requested information by continent, but this not their request.

Anyway, we are almost done with this problem. On the data ribbon you should see subtotals. Let's click this and enter the info as indicated below. We want to create subtotals based on book names and need subtotals where te names change. We want t sum our results and you can;t mix and match i.e. it's either sum or average or multiply for every column chosen, and finally, what columns to do these calculations on. So, below left, we have prepped subtotals in its dialog box. Below right you can see the results which we will talk abou below these pictures.

Move your cursor anywhere on the first column of books. In fact, we'll split this up so that we will all see the same result even though we start from a different place. Click a to z. below is a composite of the result.

Notice hte grouping of the titles. We are almost done. We need the system to give us subtotals on these grouped titles. The data ribbon provides a tool for this which you can see far right. This is sub totals and clicking it produces the dialog box described below. Note, however, that if you set your table ot the new table construct, subtotal is grayed out since the new table construct gives you many of the eatures of subtotal and much more.

Clicking yes adds a set of rows to your spradsheet. Every subtotal is added as is a grand total. In our case, there are 16 books and a grand total giving 17 new rows. In additon, a set of controls are instituted to the left. Below we see part of the spreadsheet after clicking yes to subtotals.

Look on the left at the controls. Notice that there are 3 of these (1,2,3) at the top. 3 is detail and subtotal info. 1 is just grand total info and 2 is what we want: subtotal only info as indicated below. Notice grand total info is also included. Columns which are not used for breaking or for totalling appear unpopulated. You could group these and, in essence, hide them from view. Anyway, below is our subtotalled info.

Excel treats cells that are grouped out of view as not part of the spreadsheet when selecting. This is true in terms of sorting. There are 130 rows. In view, there are 18 at the moment. The first row is the header row, and then we have 16 rows of subtotal info and then the last row, grand totals. In a previous situation, we tried to sort with totals and totals moved through the spreadsheet. With subtotal, the system knows that grand total should be at the bottom. We would like to sort these titles on the basis of profitability - the highest at the top to the lowest. Move your cursor anywhere on the last column and click z to a in the data tab.

Now, the same principle applies to graphs. let's do a bar/column chart on the books and their respective totals worldwide. Highlight the books (except for grand total) in column A (in essence the first 17 of the rows shown) and likewise use your control key to add the first 17 cells of column J. Use the insert tab of the ribbon to create a bar chart. Below, I've gone a little further by setting an angle for the descriptors. The principle is similar to the other time we sloped text. Move your cursor on the titles and click your right button and select format axis. You will see the more modern version of the dialog box we had seen in the previous example. Click alignment and set 45 as the degrees in custom angle.

Now, a 2D approach looks at this data and determines all the possibilities of the columns in question. Here for sex, the possibilities are M and F, for state the possibilities are NJ, Ny and PA. Below we see the possibilities before doing any math.

Now the procedure would be to run through the list and add the account balance to the appropriate cell. In the first line item, we are dealing with a NY female. We would add 300 to the cell at Row 2, column 2. Suince it is initailized to 0 at the start we now have 300. If a 100 had already been there, our total would have been 400, instead.

I call this the catacomb(s) problem. Below is a part of what we are aiming at.

Paper sizes are not uniform around the world and Word is designed to be able to handle these other standards although your printer may no be in that position).

Let's start with a new document. If in Word already, use file/new and select a blank template. If bringing up word without selecting an existing document, Word should put you into a new document.

Let's start with an address block. We will show this to you two ways. One is by traditional paragraphs, the other using breaks. let's start with traditional paragraph ad put your hide/show on.

First paragraph is Mr. Bruce Wayne, second is 1000 Wayne Industry Drive, the third is Bat Cave, NC 28710. Your document should look like:

Mr. Bruce Wayne
1000 Wayne Industry Drive
Bat Cave, NC 28710

and you would see the end of paragraph markers at the end of each line.

We might as well use this for what I was supposed to do at the end of Monday's lecture, a discussion of cut.copy and paste.

This breaks down into the following: The clipboard and font groups are character commands. The paragraph group is paragraph commands.

Below we show this text coppied into several programs: notepad, excel and powerpoint. The clipboard is a system wide resource and can be used anywhere (and in any programs) that support text.

After the salutation we need to add another paragraph as indicated below:

Let me introduce myself. For years I was Batman’s rival, Catwoman. But, I have found a more purrfect way to earn a living.

Add this to you document and it should look like the following assuming that we have not changed font, point size, lack of underline and default coloring.

Most of the line options make sense except for 1.15. Can you guess what that might be used for. We'll discuss this in class as we end our lecture

Now, what is word art. It is a cariculture of letters. Below, we track the process of putting a Wrord art object indicating catacomb into the header.

I hope to get to this point in these classes today. If so, we;re done for the day. You can save this if you would like to your flash memory but at our next class there will be a link to load it at that point.

This script is for the Spring'13 classes at Community Colege of Philadelphia. Class scripts are in existance since Summer II 2009. To see previous scripts, and a link to prior excel tests, click here.

There will be a special review session in C3-18 Wed 10 to 12. We will be doing a previous test as chosen by Raheem Epps

About the books for CIS103 this term, Spring 2013. The book should be avaiable at the school store.

Note to my Spring'13 classes

Attention: my Email seems to be back to normal

Note for the NWRC class on Saturday, Feb 9th: Due to the storm I am aiming to start the class at 9:30. I have a bit of a drive from Chester County which will create the delay. If I am later than than that work on the myitlab tutorials. Do not do the tests.

For slide 1, here are the files

For slide 2, here are the files

For slide 3, here are the files

For slide 4, here are the files

For slide 5, here are the files

For slide 6, here are the files

For slide 7, here are the files

For slide 8, here are the files

For slide 9, here are the files

For slide 10, here are the files

yaron ben simchon.jpg
Idan raichel.jpg

The house of Willian Pitt had colors of maroon and gold. Text boxes are set to maroon and background to gold (intense yellow). We will use mouse clicks to move slides.

We think this is the 6th lecture from the end of the term, Nov 18 2010. In some places this is the beginning of Homage to Scorpios week

This script is for the Spring'13 classes at Community Colege of Philadelphia. Class scripts are in existance since Summer II 2009. To see previous scripts, and a link to prior excel tests, click here.

There will be a special review session in C3-18 Wed 10 to 12. We will be doing a previous test as chosen by Raheem Epps

About the books for CIS103 this term, Spring 2013. The book should be avaiable at the school store.

Note to my Spring'13 classes

Attention: my Email seems to be back to normal

Note for the NWRC class on Saturday, Feb 9th: Due to the storm I am aiming to start the class at 9:30. I have a bit of a drive from Chester County which will create the delay. If I am later than than that work on the myitlab tutorials. Do not do the tests.

For slide 1, here are the files

For slide 2, here are the files

For slide 3, here are the files

For slide 4, here are the files

For slide 5, here are the files

For slide 6, here are the files

For slide 7, here are the files

For slide 8, here are the files

For slide 9, here are the files

For slide 10, here are the files

yaron ben simchon.jpg Idan raichel.jpg The house of Willian Pitt had colors of maroon and gold. Text boxes are set to maroon and background to gold (intense yellow). We will use mouse clicks to move slides.

We think this is the 6th lecture from the end of the term, Nov 18 2010. In some places this is the beginning of Homage to Scorpios week

yaron ben simchon.jpg
Idan raichel.jpg

The house of Willian Pitt had colors of maroon and gold. Text boxes are set to maroon and background to gold (intense yellow). We will use mouse clicks to move slides.